MrExcel Publishing
Your One Stop for Excel Tips & Solutions

military time and colon


Posted by Brian on November 23, 2001 10:33 PM

How can I have Excel put a colon between the hours and minutes (military time) when inputting the time i.e. 1020 (10:20).


Posted by Ian Bartlett on November 26, 2001 12:33 AM

In lieu of a better answer, this might help:

(INT(A1)/24)+((A1-INT(A1))/(24*0.6))

where A1 contains the 'raw' time figure. Only thing is that you need to insert a decimal point between the hours and the minutes, ie. enter 1020 as 10.20 (or just 10.2) and it'll appear as 10:20

HTH,

Ian