Even though dates and time are actually stored as a regular number known as the date serial number, we can make use of extensive Excel date and time formatting options to display them just the way we want.
We can access some quick date and time formats from the Home tab > in the Number group:
Custom Excel Date and Time Formatting
We can also create our own custom date and time formats to suit our needs. Let's take a look.
- Select the cell(s) containing the dates you want to format.
- Press CTRL+1, or right-click > Format Cells to open the Format Cells dialog box.
- On the Number tab select 'Date' in the Categories list. This brings up a list of default date formats you can select from in the 'Type' list. Likewise for the Time category.
We aren't limited to the defaults though. You can create your own Custom date or time formats in the 'Custom' category. These custom formats are saved for you to re-use in the current file.
Custom Date Formatting Characters
Excel recognises the following characters and sets of characters for date formatting.
| Character | Explanation | Date | Formatted | |
| d | Displays the day as a number without a leading zero. | 3/09/2016 | 3 | |
| dd | Displays the day as a number with a leading zero when appropriate. | 3/09/2016 | 03 | |
| ddd | Displays the day as an abbreviation (Sun to Sat). | 3/09/2016 | Sat | |
| dddd | Displays the day as a full name (Sunday to Saturday). | 3/09/2016 | Saturday | |
| m | Displays the month as a number without a leading zero. | 3/09/2016 | 9 | |
| mm | Displays the month as a number with a leading zero when appropriate. | 3/09/2016 | 09 | |
| mmm | Displays the month as an abbreviation (Jan to Dec). | 3/09/2016 | Sep | |
| mmmm | Displays the month as a full name (January to December). | 3/09/2016 | September | |
| mmmmm | Displays the month as a single letter (J to D). | 3/09/2016 | S | |
| yy | Displays the year as a two-digit number. | 3/09/2016 | 16 | |
| yyyy | Displays the year as a four-digit number. | 3/09/2016 | 2016 | |
Custom Date Formatting Examples
We can bring the characters together to create our own custom formats. Some examples below:
Remember; the custom format doesn't alter the underlying date serial number, it is still the same.
Custom Time Formatting Characters
Like dates, time also has its own set of custom formatting characters, as listed below:
| Character | Explanation | ||
| h | Displays the hour as a number without a leading zero. | ||
| [h] | Displays elapsed time in hours. If you are working with a formula that returns a time in which the number of hours exceeds 24, use a number format that resembles [h]:mm:ss or [h]:mm | ||
| hh | Displays the hour as a number with a leading zero when appropriate. If the format contains AM or PM, the hour is based on the 12-hour clock. Otherwise, the hour is based on the 24-hour clock. | ||
| m | Displays the minute as a number without a leading zero.* | ||
| [m] | Displays elapsed time in minutes. If you are working with a formula that returns a time in which the number of minutes exceeds 60, use a number format that resembles [mm]:ss. | ||
| mm | Displays the minute as a number with a leading zero when appropriate.* | ||
| s | Displays the second as a number without a leading zero. | ||
| [s] | Displays elapsed time in seconds. If you are working with a formula that returns a time in which the number of seconds exceeds 60, use a number format that resembles [ss]. | ||
| ss | Displays the second as a number with a leading zero when appropriate. If you want to display fractions of a second, use a number format that resembles h:mm:ss.00. | ||
| AM/PM, am/pm, A/P, a/p | Displays the hour using a 12-hour clock. Excel displays AM, am, A, or a for times from midnight until noon and PM, pm, P, or p for times from noon until midnight. | ||
*Note: The m or mm code must appear immediately after the h or hh code or immediately before the ss code; otherwise, Excel displays the month instead of minutes.
Custom Time Formatting Examples
Note: if your PC region settings have the Date & Time formats set to show the Short Time as hh:mm tt or the Long Time as hh:mm:ss tt then this may override any single 'h' formats and display them as 'hh'.
The screenshot above is what I see with my PC region settings for the Short Time as h:mm tt. If you see something different when using a single ‘h’ format, then it will be down to your PC region settings.
More Excel Formatting
Custom cell formatting isn't limited to dates and times. There is a plethora of formatting options for all types of numbers that we can use to get our reports looking just the way we want. Click here for our comprehensive guide to Excel custom number formatting.
Free eBook - Working with Date & Time in Excel
Everything you need to know about Date and Time in Excel - Download the free eBook and Excel file with detailed instructions.
Enter your email address below to download the comprehensive Excel workbook and PDF.
Leave a Reply