Solved

Covert 10 digit number into date and time in Excel

Posted on 2011-03-17
5
902 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
5 Comments
 
LVL 11

Assisted Solution

by:SeanStrickland
SeanStrickland earned 200 total points
Comment Utility
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
Comment Utility
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:Michael74
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks for the help!! It took both to resolve.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

763 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

6 Experts available now in Live!

Get 1:1 Help Now