UTC (Universal Time) conversion

Question

Level 1

8 points

UTC (Universal Time) conversion

Hello;

I have a column of numbers in UTC (e.g. 1262840361) that I want to convert to a readable human time. How do I do that in Numbers '09?

Thanks

PRO, Mac OS X (10.6.2), Numbers 09

Posted on Jan 6, 2010 10:05 PM

Reply

Answer 1

OhBoy001 Author

Level 1

8 points

Jan 6, 2010 10:36 PM in response to OhBoy001

I think it is (DATE(1970,1,1)+((UTC-(8*3600))/86400)

Reply

Answer 2

OhBoy001 Author

Level 1

8 points

Jan 6, 2010 10:43 PM in response to OhBoy001

Yes, that is the correct formula. Would be great if a function call UTC existed to automatically due the conversion, after all, UTC is popular

Reply

Answer 3

Barry

Level 9

60,023 points

Jan 7, 2010 2:40 AM in response to OhBoy001

OhBoy001 wrote:
Yes, that is the correct formula. Would be great if a function call UTC existed to automatically due the conversion, after all, UTC is popular

In Numbers, go to the Number menu, choose Provide Numbers Feedback.

Make your request using the form provided.

Requests made there go directly to Apple.

Comments that (It "would be great if..." made here are read by users like you, but are not regularly monitored by Apple.

Regards,
Barry

Reply

Answer 4

Jan 7, 2010 5:29 AM in response to OhBoy001

OB,

Can you cite a reference where your UTC format is explained? I'm curious about the derivation.

Jerry

Reply

Answer 5

KOENIG Yvan

Level 8

41,819 points

Jan 7, 2010 8:47 AM in response to Jerrold Green1

Hello Jerrold

I found it in Wikipedia:
http://fr.wikipedia.org/wiki/Tempsuniverselcoordonné
http://fr.wikipedia.org/wiki/ISO_8601

Yvan KOENIG (VALLAURIS, France) jeudi 7 janvier 2010 17:47:50

Reply

Answer 6

OhBoy001 Author

Level 1

8 points

Jan 7, 2010 9:10 AM in response to Jerrold Green1

Formula explained:

UTC is the number of seconds that have expired from GMT Jan 1, 1970 @ 12:00:00am

The formula takes the date of 1.1.1970 12:00:00am GMT (DATE(1970,1,1) and adds the number of seconds (UTC) to it with an adjustment for time zone.

The time zone adjustment is the (8*3600) where 8 represents the time zone offset from GMT (8=PST).

The division of 86400 takes the UTC (as in integer in seconds) and converts it into days.seconds format that Numbers uses. 86400 is the number of seconds in a day.

Numbers handles seconds as the fractional part and days and the integral part of a floating point number.

Reply

Answer 7

Jan 7, 2010 9:24 AM in response to OhBoy001

Thanks OB,

I inferred all that except the Time Zone correction that you are making. I was confused since your UTC didn't include the Zulu designator and you didn't specify your zone. I should have gotten a clue from your Profile Location.

Jerry

Reply