convert serial number to hr min sec in excel 2003

[greyscale]

Hi, I am getting a list of numbers in a data feed that i believe may be serial numbers for time values. e.g. I think that:

1323648006 = 12:00:06 AM
1323651792 = 1:03:12 AM

Can anyone help me with the calculation to convert them into the hh:mm:ss format that i need?

Thank you

[pike]

greyscale

0.0000694444444444444=12:00:06 AM


what makes you think that they are Time?

[greyscale]

THe data feed has a header that says 'event time' and the last few digits mostly match the seconds from the report that pulls data from the feed. I have attached a sample. the item in pink is the 'event time' directly from the data feed. The corresponding entry in yellow is the time as shown in the vendor report against that value. We are building our own reporting interface so need to use the data feed, and not the report from the vendor interface.

[greyscale]

It won't seem to let me attach so i've had to paste it below but you're right. It's not the serial number. I don't know what it is. If you start from the right of the second column you can see that 006 = 12:00:06 and that 169 (4th line) seems to equal 169s which is 2m49 which equals 12:02:49. Could 1323648 equal the 12th Dec 2011? All these times are from 12th dec so it seems that as the time gets later it starts changing more of the digits towards the left so that 648 becomes 649 becomes 650 etc. Very odd. Don't know how to convert this.

Time from trace report event_time
12:00:06AM 1323648006
12:00:18AM 1323648018
12:00:41AM 1323648041
12:02:49AM 1323648169
12:03:54AM 1323648234
12:03:58AM 1323648238
12:05:31AM 1323648331
12:05:31AM 1323648331
12:08:04AM 1323648484
12:08:06AM 1323648486
12:08:06AM 1323648486
12:10:18AM 1323648618
12:10:18AM 1323648618
12:18:03AM 1323649083
12:19:50AM 1323649190
12:19:53AM 1323649193
12:28:29AM 1323649709
12:28:29AM 1323649709
12:34:22AM 1323650062
12:34:33AM 1323650073
12:34:35AM 1323650075
12:35:16AM 1323650116
12:35:16AM 1323650116
12:36:28AM 1323650188
12:36:29AM 1323650189
12:36:30AM 1323650190
12:40:18AM 1323650418
12:40:18AM 1323650418
12:44:26AM 1323650666
12:45:22AM 1323650722
12:45:25AM 1323650725
12:45:26AM 1323650726
12:45:26AM 1323650726
12:46:53AM 1323650813
12:47:09AM 1323650829
12:48:45AM 1323650925
12:48:47AM 1323650927
12:48:49AM 1323650929
12:52:13AM 1323651133
12:52:13AM 1323651133
12:53:22AM 1323651202
12:54:11AM 1323651251
1:03:12AM 1323651792

[Haseeb Avarakkan]

Hello greyscale,

That should be UNIX date/Time. created by other computer programs.

Say the digits like 1323648006, 1323648018 etc are in A2 to down. so try this formula in B2 & copy down.

=A2/86400+DATE(1970,1,1)

format as hh:mm:ss

See the below link.

UNIX Date/Time

[pike]

or for sydney where a1 has the value
=A1/86400+25569+0.416666666666667
or =A1/86400+"1/1/70" which is 10 hours less

with custom format d/mm/yyyy h:mm:ss

result 12/12/2011 10:00:06 AM or 12/12/2011 00:00:06 AM

[greyscale]

Perfect! You're both lifesavers! Thanks very much.

[pike]

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save