Home › Forums › Mooshimeter Support › How to convert CSV?

- This topic has 0 replies, 1 voice, and was last updated 6 years, 10 months ago by admin.

- AuthorPosts
- UliGuest
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.

Uli - AnonymousGuest
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. - AnonymousGuest
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! - AnonymousGuest
Oops I was off by 2 days on the 70 year offset use 2209161600 seconds instead.

- AnonymousGuest
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) - AnonymousGuest
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.

Uli - AnonymousGuest
It is all ok now, Owen,

for me here in Germany the formula is:

=(((A2/60000)/60)/24)+DATUM(1970;1;1)+(1/24)

You got me on the right path,

but I suppose there are still a lot of interesting problems

to solve.

Uli - adminKeymaster
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:

http://spreadsheetpage.com/index.php/tip/converting_unix_timestamps/

Best

~James

- AuthorPosts