Solved

Covert 10 digit number into date and time in Excel

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering 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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

696 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