displaying time in 24 hour clock

[Titian1997]

I am trying to use the 24 hour clock to display flight departure and arrival times. I want to enter 4 consecutive digits eg: 0745 and have it displayed as 07:45. I have tried the formulas and followed the help menu but I can't make it work. When the cell is holding the formula, when I type in the 4 digits, it returns 00:00 - which is the right format but not reflecting the time entered. Please can someone help me with this? I have spent quite a bit of time trying to figure this out but it is beyond me. 

Thanks very much. 

Christina. 

[#2]

Hi Christina, just right click on the cell, then format cells, then choose custom , then choose h:mm it will work, usually excel considers time as fraction of 24 hours, so you need to choose h:mm format for proper display of time

[#3]

Hi @chahine, I have tried that but it doesn't keep the lead 0 for am times. So if I input 0800, it presents as 00:00. I don't necessarily need the colon separating the hours and minutes. I'd just like to keep the lead 0s. I hope this makes sense. 

Thank you for your help. 

Kind regards

Christina

[#4]

@Titian1997 put in your formatting hh:mm (if you need 24hrs format) or hh:mm AM/PM , but ensure when you are inputting time ":" should be there 

[#5]

if you just want regular numbers in formatting put 0000 then even if you dont put colons your number will have 4 digits , for example 745 will be 0745 , 830 will be 0830, hope this what you need

[#6]

@Titian1997 Excel sees times as fractions of one day containing 24 hour. Eight o'clock in the morning (08:00) is equivalent to 0.33333etc, meaning 1/3 into a day of 24 hours. Likewise, a time of 13:25 equals the number 0.55902778etc (13 hrs X 60 min + 25 min = 805 minutes out of 1440 minutes per day)

Furthermore, the Excel calendar starts counting at 1 for January 1, 1900. When you enter 0800 in a time formatted cell, Excel sees that as day number 800 (i.e. March 10, 1902) at midnight and just displays the Time component, being 00:00.

I've attached workbook with an example that may be useful to you. You enter four digits in columns A and B. These columns are custom formatted to always show 4 digits. Enter 0410, Excel will get rid of the leading zero but the custom format will show it anyway. Columns C and D contain formulae that "translate" the numbers from columns A and b to fractions of one day that can be represented as a valid time. Finally, Column E calculates the duration between Departure and Arrival time. Not something you asked for but it shows how you can calculate with time.