1. Windows Central Question's Avatar
    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!
    07-13-2015 04:03 PM
  2. Marko O's Avatar
    It is a windows timestamp.

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

    -Marko-
    tdpaul7 likes this.
    09-26-2015 03:39 AM
  3. tdpaul7's Avatar
    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!!
    10-09-2015 09:00 AM
  4. DJBoozy's Avatar
    can you give an example for this? thanks...
    11-02-2015 09:45 PM
  5. anon(5981118)'s Avatar
    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.
    Harrie-S likes this.
    01-19-2016 09:34 AM

Similar Threads

  1. Why can't I update my Lumia 520 to the latest version of Windows 10 ie 10166?
    By Chandrasekhar mahakulkar in forum Windows 10 Mobile Insider Preview
    Replies: 9
    Last Post: 07-29-2015, 01:42 PM
  2. What is this green line passing through middle of my Lumia 930?
    By Sunandan Mahant in forum Nokia Lumia 930
    Replies: 2
    Last Post: 07-14-2015, 03:35 AM
  3. How to get rid of old apps?
    By Dirk Bromberg in forum Windows 10 Mobile Insider Preview
    Replies: 4
    Last Post: 07-13-2015, 03:16 PM
  4. SMS backup confusion...
    By tpapictures in forum Nokia Lumia 925
    Replies: 1
    Last Post: 07-13-2015, 01:09 PM
  5. Why can't I text to a contact phone number?
    By Windows Central Question in forum Nokia Lumia 928
    Replies: 1
    Last Post: 07-13-2015, 12:24 PM
LINK TO POST COPIED TO CLIPBOARD