Converting dates/times to readable format

[dapmco]

I would like to export my reservations with dates and times in a readable format. How do I convert them from something like


start_date   end_date   starttime   endtime
1158379200   1158379200   1140   1170

To

August 3,2007 August 3,2007    8am  12pm

Obviously the readable isnt correct.

thanks for any help

[dapmco]

I  figured out a solution to get it to excel.

G4 is cell being converted
date conversion
=DATE(1970,1,1) + G4/86400

I4 is cell being converted
time conversion
=TIME((I4/60),((I4/60)-(INT(I4/60)))*60,0)

Im sure there is a way I could have done this with sql but didnt know how..

[BalotaBJ]

Thank you for posting this. I know it was posted quite sometime ago. Hey, it still helped me a lot. Thank you.

In converting the time to readable format, you might also want to try this:

I4 is cell being converted

=I4/1440
e.g. I4 = 915
915/1440 = 0.63542
*Change to format to time, it should show 3:15 PM.

time conversion
=TIME((I4/60),((I4/60)-(INT(I4/60)))*60,0)

[Nick]

Very helpful.  Thank you for posting this!