Following on from our previous article about using custom formatting in Excel, this time we will look at how to change the formatting of your dates and times to get the effects that you want.
Custom formats allows you to define the way that numbers, text, date or time are shown on the screen. What it doesn’t do is change the actual value that is in the cell, but rather, it shows something different on top which ‘masks’ the real value in the cell. If you aren’t familiar with custom formats, I recommend that you read part one of this article before continuing.
Date & time operators
The basic operators used for formatting date and time are:
- h or hh = Hours 0–23
- m or mm = Minutes 0–59
- s or ss = Seconds 00–59
- h AM/PM = Time to the nearest hour e.g. 4 AM
- h:mm AM/PM = Time to the nearest minute e.g. 4:36 PM h:mm
- h:mm:ss A/P = Time to the nearest second e.g. 4:36:03 P
- d or dd = Day placeholder with the day as a number 1-31
- ddd or dddd = Day placeholder with day written in short or long format e.g Tues or Tuesday
- m or mm = Month placeholder with the month as a number
- mmm or mmmm = Month placeholder with month written in short or long format
- yy or yyyy = Year placeholder with the year in short or long format
Composition of your custom format
In the scenario below we want to change our order dates in column A so that they include the day of the week they were ordered and thereby identify weekend orders which will incur a surcharge
So we use the format:
ddd dd mmm yyyy
- Expression 1 (ddd) tells us to include the date written in short format (e.g. Mon, Tues, Wed etc.)
- Expression 2 (dd) tells us to use the day of the month as a number (1-31)
- Expression 3 (mmm) tells us to use the month written in a shot format (e.g. Jan, Feb etc.)
- Expression 4 (yyyy) tells us to include the full year
Using the custom formats feature
- Highlight the cell or cells that you want to format
- From the main ribbon select Home tab > Numbers group > click the icon in the bottom right hand corner of the group. This displays the Format Cells dialog box
- From the categories on the left hand side select Custom. You’ll then see various options to choose from.
- You may choose one of the preset custom formats (of which there are many) or alternatively you may create your own by constructing a formula in the ‘type’ text box (you can see our formula typed into the text box in the example below
- When you are happy, click ‘ok’ and you’ll see your highlighted cells are now showing the new format.