Solved

Covert 10 digit number into date and time in Excel

Posted on 2011-03-17
5
935 Views
Last Modified: 2012-06-21
I'm trying to convert a ten digit code into a date and time but more importantly time. These numbers are being extracted from an older Access Database.

Starttime - 1300371878 | Endtime - 1300371920
Starttime - 1300386867 | Endtime - 1300386893

If this helps any. I know that for a fact that:
1300371878 represents 7:24:55 AM
0
Comment
Question by:autocrib
[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
  • Learn & ask questions
5 Comments
 
LVL 11

Assisted Solution

by:SeanStrickland
SeanStrickland earned 200 total points
ID: 35162558
My guess is that it's a UNIX Timestamp (a lot of older systems and most mySQL databases or PHP developers would use this).

http://spreadsheetpage.com/index.php/tip/converting_unix_timestamps/
0
 
LVL 11

Expert Comment

by:SeanStrickland
ID: 35162582
To expand on that a little...

What is a UNIX Timestamp?
"If you import data you might encounter time values stored as Unix timestamps. Unix time is defined as the number of seconds since midnight (GMT time) on January 1, 1970 -- also known as the Unix epoch."

How to convert?
If your numeric value was in cell A1, the formula to convert it would be:
=(((A1/60)/60)/24)+DATE(1970,1,1)

Open in new window


Divide it by 60 to get # of minutes since 1/1/1970.
Divide it by 60 again to get # of hours since 1/1/1970.
Divide it by 24 to get # of days since 1/1/1970.
Add the date 1/1/1970 to the value that remains to get today's date.
Make sure you format as a date/time field so the value makes sense (otherwise it reads similar to 39476.48).
0
 
LVL 23

Expert Comment

by:Michael Fowler
ID: 35162625
It looks like a Unix date value, if so then time zones will come into play. This should help you

http://www.experts-exchange.com/Programming/Languages/Visual_Basic/Q_25093826.html

Michael
0
 
LVL 50

Accepted Solution

by:
barry houdini earned 300 total points
ID: 35164359
My formula solution in the link that Michael posted is essentially the same as Sean's above.....but with an additional 1 hour adjustment for time zone.

For your example above that could be amended to cope with a 7 hour adjustment, e.g. with 1300371878 in A2 you could use this formula to convert and adjust by 7 hours

=DATE(1970,1,1)+A2/86400-"7:00"

If you format result cell as m/d/yyyy hh:mm:ss that will give you 3/17/2011 07:24:38

....but just subtracting 7 hours doesn't take into account daylight saving time, whereby the difference between UTC/GMT and PST will be 7 hours in the "summer" (during daylight saving period) and 8 at other times. So if you want to take that into account I'd suggest a two-step formula approach.

In B2 to convert to a recognisable date/time in GMT

=DATE(1970,1,1)+A2/86400

and then this formula in C2 to perform a further conversion to PST (taking into account DST)

=B2-LOOKUP(B2,DATE(YEAR(B2),{1,3,11},{1,15,8})-WEEKDAY(DATE(YEAR(B2),{1,3,11},7))+{0,10,9}/24,{8,7,8})/24

format both as d/m/yyyy hh:mm:ss

Note that this conversion uses the current US DST rules. As they were introduced in 2006 that should be valid for all data in 2007 and later (or until the rules change again).

See attached examples

regards, barry
Time-Conversion.xlsx
0
 

Author Closing Comment

by:autocrib
ID: 35169772
Thanks for the help!! It took both to resolve.
0

Featured Post

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!

Question has a verified solution.

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

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 article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

688 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