# how to convert 13 digit number into Timevalue

1. ## 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.  Register To Reply

2. ## 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?  Register To Reply

3. ## Re: how to convert 13 digit number into Timevalue Originally Posted by Sam Capricci 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).  Register To Reply

4. ## 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?  Register To Reply

5. ## Re: how to convert 13 digit number into Timevalue Originally Posted by Sam Capricci 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)  Register To Reply

6. ## 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.  Register To Reply

7. ## 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  Register To Reply

8. ## 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!  Register To Reply

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