Need Formula to convert seconds to dd/mm/yyy 00:00:00 format

[sjgummelt]

I have a column of serial numbers that represent date/time in seconds. This column of time is the result of a ODBC connection to a Oracle DB Table in one of our Genesys databases.

Here are few examples.
1327932243
1327934323
1327935422
1327935705

I would like to know how to convert these serial numbers to a mm/dd/yyyy hh:mm:ss format.

Thanks.

Sandy

[Jakobshavn]

With data in A1 on down, in B1 enter:

=A1/(60*60*24) then format Custom "mm/dd/yyyy hh:mm:ss"

To display:

01/28/1942 14:04:03
01/28/1942 14:38:43
01/28/1942 14:57:02
01/28/1942 15:01:45

[martindwilson]

is that unix time?
=A1/(60*60*24)+"1/1/1970"

[etaf]

you mean into days hours an mins

divide by 86400 to get days and format as DD hh:mm:ss
so =A1/86400 and format custom: dd hh:mm:ss

1327932243 = 28days 14hrs 04min 03seconds

1327935705 = 28 15:01:45

[sjgummelt]

The formula Martin posted works great, Thanks.

However, this formula returns the time in the GMT 0 time zone. Sorry to ask, but what change do I need to make to convert to GMT-6 timezone?

I verified the time stamp using GMT -6 timezone and that matches the auto generated reports. This formula is to convert the time format I get from the database.

Thanks again for such a quick response.

This is the link I found that will convert the timestamp just to verify. The formula I need for my spreadsheet.
http://www.timestampgenerator.com/da...mp/1362138581/

martindwilson
Re: Need Formula to convert seconds to dd/mm/yyy 00:00:00 format

is that unix time?
=A1/(60*60*24)+"1/1/1970"

[Jakobshavn]

Most of Texas (eastern part) is 6 hours behind GMT. So if it is 11:00 AM in Greenwich, it would be 5:00 AM in Texas, so:

=A1/(60*60*24)+"1/1/1970"-.25

See if this works for you.

[sjgummelt]

Yes, that formula worked perfectly! Thank you so much.

[Jakobshavn]

You are quite welcome. Thanks for the feedback.