Is there a LocalTimeStamp field of Contacts+Message Backup?

[Windows Central Question]

"LocalTimeStamp" field of Contacts+Message Backup

MS FINALLY came up with a way for us to backup and export our text messages, and I was (an still am) so excited. I felt this was a huge drawback of the Windows Phone platform.

Anyway, I imported my SMS backup file (.msg) into Excel, so I could manipulate the file. It wasn't too hard to decipher each field in the spreadsheet, except the "LocalTimeStamp", which is an 18-digit number ending in "000". It's definitely not the YYYYMMDD...etc format. I'm guessing it's an elapsed seconds field, as the number increases with time. I just can't figure out how to convert it to anything useful (i.e. year, month, day, time, etc).

Can anyone provide some type of conversion for this huge number?

Many thanks from a very excited WP user!

 

[Marko O]

It is a windows timestamp.

To convert it to unix time stamp do:
timestamp / (10*1000*1000)-11644473600

-Marko-

 

[tdpaul7]

I've been looking for this as well. Thanks to -Marko- for providing the first part. I've come up with the following MS Excel formula that converts the Windows Phone 8.1 (and probably other versions) to a correct date and time for my local timezone.

=((((F2/(10*1000*1000)-11644473600)/60)/60)/24)+DATE(1970,1,1)-TIME(6,0,0)

The part of the formula for the timezone "-TIME(6,0,0) is the portion I had to alter to make it show my local (Mountain Standard Time).

Hope this helps others!!

 

[DJBoozy]

can you give an example for this? thanks...

 

[anon(5981118)]

Hey, just wanted to say thanks. I downloaded SMS messages and I needed to preserve date/time stamps in a readable fashion, so I did a web search and this solution came up and worked perfectly! Thanks to Excel for recognizing the file type and formatting it reasonably, and to you for this formula.