long datatype to date string

  • Thread starter Thread starter Candy
  • Start date Start date
C

Candy

Hello,

In my cell I have a long datatype value, like 1046101500. Now I want to have
this converted to a
date string like 'dd/m'/yyyy hh:mm:ss', so my example would be converted to
'24/02/2003 15:45:00'.
Is this possible?

Andy
 
Candy

How does your data type correspond to the date? Where is the year? Month?
etc.

Andy.
 
Candy,

=1046101500/(24*60*60)+DATEVALUE("01-Jan-1970")

and format the cell to show the date & time of your choice.

regards,

JohnI
 
Candy

Dividing the 'seconds' by 86400 (60 seconds in a minute, 60 minutes in an
hour, and 24 hours in a day) gives you the number of days (12107.66) When
this is added to 01/01/1900, however, it gives the result of 22/02/1933
15:45pm.

Andy.
 
Candy

Dividing the 'seconds' by 86400 (60 seconds in a minute, 60 minutes in an
hour, and 24 hours in a day) gives you the number of days (12107.66) When
this is added to 01/01/1900, however, it gives the result of 22/02/1933
Click to expand...

Not 1900 but 1970. Unix stores time as number of seconds since
1/1/1970.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
Back
Top