[Webinar] Streamline your web hosting managementRegister Today

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 348

# 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

Rene
0
ReneGe
• 4
• 2
• 2
2 Solutions

IT ConsultantCommented:
Those numbers look like Unix time, only it starts from 1 January 1980 instead of 1970.

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
0

IT ConsultantCommented:
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
0

ArtistCommented:
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).
0

IT ConsultantCommented:
Unix time yes, his numbers no. If you test, 765121379 is 31 Mar 1994 13:42:59 on Unix time.
0

ArtistCommented:
Unix time starts 1/1/70:  http://en.wikipedia.org/wiki/Unix_time

My guess would be that his conversions are incorrect.  However, they seem to be 20 years off of what I get for the conversions, so a simple adjustment would work if in fact that is what he needs.
0

IT ConsultantCommented:
Oops. The start date is January 2nd, 1990 :)

Revised file attached.
not-unix-time.xlsx
0

Author Commented:
You nailed it!

Thanks to you all :)
0

Author Commented:
I am very grateful!
Thanks again :)
0

## Featured Post

• 4
• 2
• 2
Tackle projects and never again get stuck behind a technical roadblock.