asked on

how to convert a decimal number in excel to date and time

With a have excel column with number like 1417528069
1417544959
These number are actually represent date and time in an sybase table column.
how can these numbers be converted to date and time.

SOLUTION

Hakan Yılmaz

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

barry houdini

Hello Rickgov,

Do you know which date/time values those convert to?

What do you want 1417528069 to convert to? What about 1417544959?

I don't know how to convert those......but if you can give some sample conversions it should be possible to use an excel formula to replicate that

regards, barry

SOLUTION

Danny Child

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Tony Pitt

I believe Sybase stores dates based on 1753. Therefore these numbers will be something like number of days since 1 Jan 1753, but we need to know what certain integers represent to be able to work out exactly what.

/T

ASKER CERTIFIED SOLUTION

barry houdini

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Tony Pitt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Shen

ASKER

I tried the formula=DATE(1970,1,1)+A2/86400 and the format d-mmm-yyyy hh:mm:ss and is close.

We have a record with 1425320708 that should output: 2-Mar-2015 1:25:08 PM. However, when i run the formula and the format it outputs: 2-Mar-2015 06:25:08 PM. It has the gmt 5 hours difference. Is there any away to adjust the formula to display local time

Shen

ASKER

it iseens to be okay now. i subtract form the formula time(5,0,0).

Shen

ASKER

thanks for the help