Solved

Covert 10 digit number into date and time in Excel

Posted on 2011-03-17
5
912 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
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:Michael74
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

Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

809 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