[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now


Date conversion

Posted on 2014-04-04
Medium Priority
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).
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

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 400 total points
ID: 39978024
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.
LVL 35

Accepted Solution

Dan Craciun earned 1600 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

656 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