Solved

Date conversion

Posted on 2014-04-04
8
326 Views
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,
Rene
0
Comment
Question by:ReneGe
  • 4
  • 2
  • 2
8 Comments
 
LVL 34

Expert Comment

by:Dan Craciun
Comment Utility
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 34

Expert Comment

by:Dan Craciun
Comment Utility
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

by:yodercm
Comment Utility
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 34

Expert Comment

by:Dan Craciun
Comment Utility
Unix time yes, his numbers no. If you test, 765121379 is 31 Mar 1994 13:42:59 on Unix time.
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 27

Assisted Solution

by:yodercm
yodercm earned 100 total points
Comment Utility
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 34

Accepted Solution

by:
Dan Craciun earned 400 total points
Comment Utility
Oops. The start date is January 2nd, 1990 :)

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

Author Comment

by:ReneGe
Comment Utility
You nailed it!

Thanks to you all :)
0
 
LVL 10

Author Closing Comment

by:ReneGe
Comment Utility
I am very grateful!
Thanks again :)
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Iteration: Iteration is repetition of a process. A student who goes to school repeats the process of going to school everyday until graduation. We go to grocery store at least once or twice a month to buy products. We repeat this process every mont…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

728 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now