Forum Discussion
displaying time in 24 hour clock
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
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.
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
- 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
