excel formatting how to

Excel Custom Time Formatting

How-To

how to time format excel

In order to create a custom number format, you’ll need to access the Format Cells
window. Once there, choose the “Custom” option from the left hand side. Under the word “Type” you’ll be typing in your custom number format.

How to Type the Format

Customizing time works a lot like customizing dates. However Excel rolls over the clock after 23 hours, so we’ll need to circumvent that. Let’s start with the basic characters.

Formatting Characters

Hours, Minutes, Seconds

Put in the following codes on the left to achieve the look on the right

HH                      01 thru 23

H                            1

MM                     01 thru 59

M                           1 thru 59

SS                          01 thru 59

S                             1 thru 59

AM/PM           Also am/pm, A/P, a/p

Most of us don’t need to see the seconds, or maybe even the minutes, but we still want Excel to count them. But we’ll customize this to show Hours and Minutes and label each.

H“Hrs” MM“Min”             to see              15

Beyond 24 hours

If we’re doing time management, we’ll find it problematic is Excel rolls over at 24 hours. Use the square brackets to surround the hours to get the hours to display a number larger than 23. You can also use this for minutes if you don’t want them to roll over.

[H]:MM:SS                    will display             time edit excel

Keep in mind that Excel is still going to roll-over the value, it just won’t display it. Excel still stores time value as days and hours. And as you may already know Excel starts counting time from January 1st 1900. So this means Excel will actually store the above cell as 1/1/1900 at 11am.

Leave a reply:

Your email address will not be published.

Sliding Sidebar

Archives