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

x
Solved

# Date conversion

Posted on 2014-04-04
Medium Priority
342 Views
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
Question by:ReneGe
[X]
###### 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
• 4
• 2
• 2

LVL 35

Expert Comment

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

HTH,
Dan
0

LVL 35

Expert Comment

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 :)
not-unix-time.xlsx
0

LVL 27

Expert Comment

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

LVL 35

Expert Comment

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

LVL 27

Assisted Solution

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.
0

LVL 35

Accepted Solution

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

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

LVL 10

Author Comment

ID: 39978051
You nailed it!

Thanks to you all :)
0

LVL 10

Author Closing Comment

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

## Featured Post

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…
###### Suggested Courses
Course of the Month14 days, 2 hours left to enroll