MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Time Number formatting

Posted by Safa Egilmez on June 27, 2001 7:27 AM

I am trying to get Excel 2000 to display and accept military time in my workbook. Everytime I enter "0000" hours, it converts it to "0". I went through all of the formatting options and still does not happen. I then set it as a text option but then when I chart it, the y-axis still does not display the "0000". I an trying to create a daily work schedule and shift coverage by using stacked bars so I do need the military time there. Help!

Posted by Russell on June 27, 2001 7:46 AM

Try formatting your cells as "hhhh"

If this isn't what you're looking for, please post a sample of your data.

Hope this helps,


Posted by Mark W. on June 27, 2001 10:22 AM

Instead of 0000 don't you mean 2400?

Posted by Safa Egilmez on June 27, 2001 11:14 AM

Well the timeline starts from 0000 hours and continues until 2400 (even though both are the same). The only problem that I had when I formatted the cells with hhhh is that the first two digits show and the last two do not show. I then tried it with hhmm and still does not work. My supervisor vehemently opposes me using hh:mm format. This correctly displays the time but places a ":" between hours and minutes. He told me to just palinly "Fix it no matter what". Any other ideas?

Posted by Russell on June 27, 2001 11:23 AM

Do you have pre-existing data? If so, how does it look (can you give a sample)? If not, you can enter minutes by entering a time WITH the colon in the time. If you format your cells with hhmm and then enter the time with the colon (12:30), it will work.

Posted by Safa Egilmez on June 27, 2001 12:04 PM

I have fixed both the formatting and the Chart problem. I have overlooked that when I tried to do the hhmm format, I accidently chose the hhhh format again and that was the reason why it was only showing the first part of the hour. I have selected hhmm format and now it displays all of the numbers. Thank you very much for all the help.