CONVERT NUMBER TO TIME

Posted by JIMINEY DEHEY on November 06, 2001 10:29 AM

I need a formula that will covert a 4 digit number to time for example 1929 will become 19:29 (military time). Anyone??

Posted by Barrie Davidson on November 06, 2001 10:37 AM

Try using

=VALUE(LEFT(A1,2)&":"&RIGHT(A1,2))

and format your cell for hh:mm. Of course, this assumes that your number will contain leading zeros (08:30 for example).

Regards,
Barrie

Barrie Davidson

Posted by Mark W. on November 06, 2001 10:48 AM

=SUM(MID(A1,{1,3},2)/{24,1440})

Posted by IML on November 06, 2001 10:55 AM

Short hand for =MID(A1,1,2)/24+MID(A1,3,2)/1440? Very nice!