ReneGe
asked on
Date conversion
Hi there,
I have a strange date and time format that I need to convert the date to Gregorian and time in decimal in an Excel 2010 formula.
Here are a few examples:
765121379 = 2014-03-31,01:42 PM,13.70
765192795 = 2014-04-01,09:33 AM,13.55
765212484 = 2014-04-01,03:01 PM,15.02
765212803 = 2014-04-01,03:06 PM,15.10
765230591 = 2014-04-01,08:03 PM,8.05
765367221 = 2014-04-03,10:00 AM,10.00
Thanks for your help,
Rene
I have a strange date and time format that I need to convert the date to Gregorian and time in decimal in an Excel 2010 formula.
Here are a few examples:
765121379 = 2014-03-31,01:42 PM,13.70
765192795 = 2014-04-01,09:33 AM,13.55
765212484 = 2014-04-01,03:01 PM,15.02
765212803 = 2014-04-01,03:06 PM,15.10
765230591 = 2014-04-01,08:03 PM,8.05
765367221 = 2014-04-03,10:00 AM,10.00
Thanks for your help,
Rene
Actually, the start date was January 2nd, 1980.
See attached file.
PS: if this is a homework, you can't use my solution :)
not-unix-time.xlsx
See attached file.
PS: if this is a homework, you can't use my solution :)
not-unix-time.xlsx
If your Unix time is in cell A1, then =A1/(24*60*60) + DATE(1970,1,1) should convert it; you'll have to apply a Date/Time format to the result.
Keep in mind that Unix time is GMT/UT.
(Dan, where did you get the change to 1980? Everything I can find still says Unix time originates on 1/1/70).
Keep in mind that Unix time is GMT/UT.
(Dan, where did you get the change to 1980? Everything I can find still says Unix time originates on 1/1/70).
Unix time yes, his numbers no. If you test, 765121379 is 31 Mar 1994 13:42:59 on Unix time.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
You nailed it!
Thanks to you all :)
Thanks to you all :)
ASKER
I am very grateful!
Thanks again :)
Thanks again :)
So any of those large numbers means the number of seconds elapsed since January 1st, 1980.
You have a mistake on line 2. It should be:
765192795 = 2014-04-01,09:33 AM,09.55
HTH,
Dan