Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Covert 10 digit number into date and time in Excel

Posted on 2011-03-17
5
Medium Priority
?
958 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 800 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 1200 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone 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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

610 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