+ Reply to Thread
Results 1 to 8 of 8

how to convert 13 digit number into Timevalue

  1. #1
    Forum Contributor
    Join Date
    12-03-2008
    Location
    Key West, FL
    MS-Off Ver
    365 Apps for Enterprise
    Posts
    665

    how to convert 13 digit number into Timevalue

    I have some data with timestamps in this format (The actual timevalue of this is sometime in October or November of 2020):
    Please Login or Register  to view this content.
    .

    I've found this formula and have tried to modify it to read my data, but so far, no luck (Where A1 is the raw data number):
    Please Login or Register  to view this content.
    Also I've tried this without success.
    Please Login or Register  to view this content.
    I'm pretty sure the data is in UTC but regardless of time zone, whatever formula can "read" this format should work.

    (Note: I did not attach a workbook since this is a single formula question. Hope this is OK).

    Any thoughts? Thanks.

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: how to convert 13 digit number into Timevalue

    how do you know it is sometime in October or November, is there a part of it that shows that?
    AND, I'd ask, what should the result be?
    Last edited by Sam Capricci; 11-19-2020 at 02:42 PM.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Forum Contributor
    Join Date
    12-03-2008
    Location
    Key West, FL
    MS-Off Ver
    365 Apps for Enterprise
    Posts
    665

    Re: how to convert 13 digit number into Timevalue

    Quote Originally Posted by Sam Capricci View Post
    how do you know it is sometime in October or November, is there a part of it that shows that?
    No there's nothing in it which tells me its sometime in Oct or Nov, but the source I obtained it from is a large dataset from mid-Oct to mid-Nov of this year. If it helps, I can get a number which should be on a specific day which I can identify for you. (the data is sliced into daily files).

  4. #4
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: how to convert 13 digit number into Timevalue

    There is also the second part of my question (though I added it late) what should that time value be?

  5. #5
    Forum Contributor
    Join Date
    12-03-2008
    Location
    Key West, FL
    MS-Off Ver
    365 Apps for Enterprise
    Posts
    665

    Re: how to convert 13 digit number into Timevalue

    Quote Originally Posted by Sam Capricci View Post
    There is also the second part of my question (though I added it late) what should that time value be?
    OK I just obtained a fresh number. It should be within minutes or seconds after 12:00 AM for November 18th 2020. I am guessing that this number is on UTC, which (I think) is 5 hours before ETC.
    Whoops: This is the number:
    Please Login or Register  to view this content.
    (Edit: I deleted "EST" after the 12:00 AM remark)
    Last edited by jrtaylor; 11-19-2020 at 02:54 PM.

  6. #6
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: how to convert 13 digit number into Timevalue

    tl:dr -- it looks like your number represents "milliseconds since Jan 1 1970", so any formula that will convert that number to Excel's serial time numbers (days since Jan 0 1900) will work. One possibility below.

    Your VALUE(TEXT(...)) formulas seem to be assuming yyyymmddhhmmss type integers, but that doesn't at all look correct for your two sample numbers.

    My first guess was that the serial number somehow represents seconds, so I did a =CONVERT(number,"sec","yr") to see how many years, and it is just over 50000 years -- which seems unreasonable. However, assuming the number somehow represents milliseconds =CONVERT(number,"msec","yr") gives a little over 50 years.

    An internet search found several implied ideas, but this page (https://www.jdesource.com/blog/2009/...cal-date-time/ ) makes note of pulling and converting integers that represent "minutes since midnight 1 Jan 1970 Greenwich time". Jan 1970 is a little over 50 years before today, so I hypothesize that your numbers represent milliseconds since 1 Jan 1970.

    Edit to add: Wikipedia https://en.wikipedia.org/wiki/Coordi...Universal_Time notes that one must be careful when calculating time using UTC because of the irregular spacing of leap seconds. It looks like there have been about 30 leap seconds added over those 50 years, but, without a table stating when each leap second occurred, it would be difficult to get an accurate time calculation from a pair of UTC numbers.

    There's also the possibility that it represents some kind of TAI that doesn't include leap seconds.

    That should be close, but you will need to inquire to those providing the data to really know what data they are sending you so you can treat it correctly.

    Testing, I use a formula like =CONVERT(number,"msec","day")+DATE(1970,1,1) to convert these assumed millisecond numbers to Excel's date/time calendar system and I get about 10AM on 2 Nov 2020 for the first number and just after midnight 18 Nov 2020 for the second number. If correct, then time zone somehow seems incorrect for the given UTC number, but perhaps it is correct for your purposes.
    Last edited by MrShorty; 11-19-2020 at 03:20 PM.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  7. #7
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: how to convert 13 digit number into Timevalue

    I found this formula on another site. =A1/86400000+DATE(1970,1,1)

    for your second date/time in post #5 it assigns 11/18/2020 12:02 AM if pointed at that value in cell A1 and formatted as date and time.

    from this link, it appears as Mr Shorty noted and as ChemistB noted in this link, the 86400000 must be a representation of milliseconds.
    https://www.excelforum.com/excel-for...nded-down.html
    Last edited by Sam Capricci; 11-19-2020 at 03:26 PM.

  8. #8
    Forum Contributor
    Join Date
    12-03-2008
    Location
    Key West, FL
    MS-Off Ver
    365 Apps for Enterprise
    Posts
    665

    Re: how to convert 13 digit number into Timevalue

    Hmm. I thought I replied to this thread but I don't see it when I refresh the page. So... Both formulas work! I tested out 86400000 and it indeed works out to the number of milliseconds in a 24 hour day. If you do 86400000/24 you get 0.0000002777778. Times 100 = 0.0000277777778. Then do: =CONVERT(0.0000277777778,"msec","day") you get 12:00 AM.

    So I think both formulas do the same thing. Thank you Sam and Mr. Shorty!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. convert to 2 digit number
    By pilotwings64 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 06-17-2019, 03:16 AM
  2. [SOLVED] How to auto convert last 4 digit number
    By Dean Zuki in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 09-22-2016, 11:13 AM
  3. [SOLVED] Convert any number to 3 digit only
    By analystbank in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 04-28-2016, 07:32 AM
  4. Convert 3 digit or 4 digit number to time format
    By dubcap01 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-04-2015, 04:30 AM
  5. [SOLVED] Convert number to two digit
    By mattress58 in forum Excel General
    Replies: 2
    Last Post: 10-29-2014, 10:10 AM
  6. convert 9 digit number into day and time
    By cords in forum Excel General
    Replies: 7
    Last Post: 03-17-2012, 12:45 AM
  7. [SOLVED] convert a nine digit number to base 32
    By Wildman in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 01-17-2005, 10:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1