Home › Forums › Mooshimeter Support › How to convert CSV?
- This topic has 0 replies, 1 voice, and was last updated 7 years, 6 months ago by admin.
Who could help me to convert that “1448463066.539” into UTC?
I gave it a try with EXCEL but had no success so far.
Thanks for any hint.
First divide the UTC by 86400. For example, if your 1448463066.539 was found in Cell A2 you would use this formula: =A2/86400 this would return 16764.6188256829. Then you would format you cell in the following user format MM/DD/YYYY HH:MM:SS.000 . This will return 11/23/1945 14:51:06.539
If you notice a slight time warp in this answer, I did too. How to fix this was the question I was going to ask when I found yours. I think the time is set wrong on the meter.
Update: I Figured it out!(with some help) It is simple rocket science. Here goes:
The meter bases it’s date from 0:00 GMT 1-1-1970. Excel Starts from 1-1-1900. So all you have to do is add the number of seconds from 1945 to your date (70 years or 2208988800 seconds) and then the number of seconds from GMT (I am on Pacific time -8 hours 8 * 1440 = 11520) So if your value is in cell A2, your formula for Pacific time is =(A2+22089888800-11520)/86400 Your result: 42331.4854923495 Then you apply the cell format user type MM/DD/YYYY HH:MM:SS.000 Your result:11/23/2015 11:39:06.539 Good Luck!
Oops I was off by 2 days on the 70 year offset use 2209161600 seconds instead.
Back again, after some searching I found a more elegant way to make the conversion: =(((A3/60)/60)/24)+DATE(1970;1;1)-(8/24)
The -(8/24) is the GMT offset so if you are -7 GMT it would be -(7/24)
Thanks you very much Owen.
You overtask me a bit.
I’ll ask my daugther to help me with Excel.
I am sure there are a lot of users who are very
interested in your explanations.
I didnt know so far how the clock works in the Mooshimeter.
I’ll let you know soon.
It is all ok now, Owen,
for me here in Germany the formula is:
You got me on the right path,
but I suppose there are still a lot of interesting problems
Sorry for not weighing in earlier – the time in the log is the Epoch Time, which is the number of seconds since 1970 at UTC. Here is a quick guide of how to convert in Excel: