Date conversion

Posted on 2014-04-04
Last Modified: 2014-04-04
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,
Question by:ReneGe
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
LVL 35

Expert Comment

by:Dan Craciun
ID: 39977963
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

LVL 35

Expert Comment

by:Dan Craciun
ID: 39978005
Actually, the start date was January 2nd, 1980.
See attached file.

PS: if this is a homework, you can't use my solution :)
LVL 27

Expert Comment

by:Cornelia Yoder
ID: 39978017
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).
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 35

Expert Comment

by:Dan Craciun
ID: 39978019
Unix time yes, his numbers no. If you test, 765121379 is 31 Mar 1994 13:42:59 on Unix time.
LVL 27

Assisted Solution

by:Cornelia Yoder
Cornelia Yoder earned 100 total points
ID: 39978024
Unix time starts 1/1/70:

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.
LVL 35

Accepted Solution

Dan Craciun earned 400 total points
ID: 39978031
Oops. The start date is January 2nd, 1990 :)

Revised file attached.
LVL 10

Author Comment

ID: 39978051
You nailed it!

Thanks to you all :)
LVL 10

Author Closing Comment

ID: 39978077
I am very grateful!
Thanks again :)

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below.…

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question