Microsoft Office Tutorials and References
In Depth Information
Understanding Excel Date and Time Formats
Custom number formats are entered in the Format Cells dialog. There
are three ways to display this dialog:
• Press Ctrl+1.
• From the Home tab, in the Number group, select the drop-down and
select More from the bottom of the drop-down.
• Click the expand icon in the lower-right corner of the Number
group on the Home tab.
When the Format Cells dialog is displayed, you select the Number
tab. In the Category list, you select Custom. In the Type box, you enter
your custom format. The Sample box displays the active cell with the
Imagine how strange it would be if Excel did this with regular numbers. Sup-
pose you have the number 352. Would Excel ever offer a numeric format that
would display just the tens portion of the number? If you put 352 in a cell,
would Excel display 5 or 50? It would make no sense.
Excel treats time as an extension of dates and is happy to show you only a
portion of the time. This can cause great confusion. To Excel, 40 hours really
means 1 day and 16 hours. If you create a timesheet in Excel and format the
total hours for the week as H:MM, Excel thinks that you are purposefully
leaving off the day portion of the format! Excel presents 45 hours as just 21
hours because it assumes you can figure out there is 1 day from the context.
But our brains don ’ t work that way; 21 hours means 21 hours, not 1 day and
To overcome this problem in Excel, you use square brackets. Surrounding any
time element with square brackets tells Excel to include all greater time/date
elements in that one element, as in the following examples:
• 5 days and 10 hours in [H] format would be 130.
• 5 days and 10 hours in [M] format would be 7,800, to represent that
• 5 days and 10 hours in [S] format would be 468,000, to represent that