How do I convert a serial number to the month, day, and year in E.
 

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.

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.

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.