Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]()
What is the formula for converting a date stored as a serial number in Excel
spreadsheet with Epoch date of 1/1/70 to a month,day, year? Example: Serial date 1103033227 converts to 12/14/04. Thanks. I appreciate any help. |
#2
![]() |
|||
|
|||
![]()
Assume the serial is in A1
=A1/86400+DATE(1970,1,1) ut will give you time as well but if you format it as dd/mm/yy it will display your date, if you want the pure date from midnight on that date use =INT(A1/86400+DATE(1970,1,1)) the reason you get time is that the serial date is seconds since 1/1/1970 Regards, Peo Sjoblom "Judy" wrote: What is the formula for converting a date stored as a serial number in Excel spreadsheet with Epoch date of 1/1/70 to a month,day, year? Example: Serial date 1103033227 converts to 12/14/04. Thanks. I appreciate any help. |
#3
![]() |
|||
|
|||
![]()
Hello, Judy-
I learned something new today: an Epoch date is a Unix method of time measurement. It represents the number of seconds that have elapsed since Jan 1, 1970 at midnight GMT. Convert Epoch serial date to a conventional date by determining the number of days represented by that number of seconds. 60 seconds in a minute times 60 minutes in an hour times 24 hours in a day = 86400. 1103033227 / 86400 = 12766.59. Then use Excel to add 12766.59 days to 1/1/1970, comme ca: =(1103033227/86400)+DATEVALUE("1/1/1970") Format this cell as a date, and you're done. The only other question is: do you need to represent this as your local time, or as Greenwich Mean Time? If so, you'll need to know your GMT offset: on the US East Coast the offset is 5 hours (since we're 5 hours' time zone difference between here and England). The formula is =(1103033227/86400)+DATEVALUE("1/1/1970")-(5/24) Dave O Judy wrote: What is the formula for converting a date stored as a serial number in Excel spreadsheet with Epoch date of 1/1/70 to a month,day, year? Example: Serial date 1103033227 converts to 12/14/04. Thanks. I appreciate any help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|