In custom format in Excel we discussed using the custom format feature to format text and numbers. In this article, we format date and time values and see how we can make them appear more meaningful. If you haven’t had a chance to read through the first part where we talked about basics of the custom format feature, it is highly recommended that you do so before coming back and finishing this one off.

Before we begin, again please bear in mind that the custom format feature will only change how to values appear in a cell – it will not change the underlying value. So a value of 21-Nov-2009 (40138) will remain just that no matter how you format it. Now let’s proceed.

The d – Operator

Represents the date of the month as a number. If the date is 21-Nov-2009 and the custom format specified is d, then the display output will be 21

The ddd – Operator

Represents the day of the week as shorthand text (Sun, Mon, Tue etc). If the date is 21-Nov-2009 and the custom format specified is d, then the display output will be Sat.

The dddd Operator

Represents the full day of the week (Sunday, Monday, Tuesday etc.). If the date is 21- Nov-2009 and the custom format specified is d, then the display output will be Saturday

The e Operator

Represents the year part of the date in full year format (Ex. 2009, 2010 etc.)

The h Operator

Represents the hour part of the date & time in military format. If the date is 11/21/2009 2:34:31 PM, then the display output will be 14

The m Operator

Represents that month part of the date & time as a number. If the date is 21-Nov-09, then the display output will be 11

However under the following two conditions, it represents the minute part of the date & time as a number:

1. If m or mm is preceded by h or (Ex. yyyy-h-m) or
2. If s is present anywhere in the custom format (Ex. mm-ss)

The m Operator

Represents that minute part of the date & time as a number. If the date is 11/21/2009 2:34:31 PM, then the display output will be 34

The mmm Operator

Represents that month part of the date & time as shorthand text. If the date is 21-Nov-09, then the display output will be Nov

The mmmm Operator

Represents that month part of the date & time as full text. If the date is 21-Nov-09, then the display output will be November

The s Operator

Represents that seconds part of the date & time as full text. If the date is 11/21/2009 2:34:31 PM, then the display output will be November

The yy Operator

Represents the year part of the date in shorthand text (Ex. 09, 10 to represent 2009, 2010 etc.)

The yyy Operator

Represents the year part of the date in full text (Ex. 2009, 2010 etc.)

The AM/PM Operator

Represents the meridian for the date & time. At any given instant only one of the two is displayed.

The [h] Operator

Represents the [h] hours elapsed since the starting of the Excel calendar at 00:00 hrs on 0-jan-1900. So if we were to enter a date as 1-jan-1900 it would display the number 24 when the custom format is [h]. This happens since 1-jan-1900 occurs exactly 24 hours after 0-jan-1900. (0-jan-1900 is actually not a date but more a starting point). You can get the “date” 0-jan-1900 to display correctly by using the DATE formula =DATE(1900,1,0). Then when you use the custom format [h], it will show the number of hours elapsed as 0.

The [m] Operator

Represents the [m] minutes elapsed since the starting of the Excel calendar at 00:00 hrs on 0-jan-1900

The [s] Operator

Represents [s] minutes elapsed since the starting of the Excel calendar at 00:00 hrs on 0-jan-1900

International Date and Time Format – Arabic and Buddhist

Arabic Date (Hijri Calendar)

The b2 Operator

Arabic dates (the Hijri Calendar) can be displayed by preceding the date’s custom format with the b2 operator. This works when the Arabic editing is enabled. So to display the date in Arabic date format, we could use something like b2dd/mm/yy which would display the date as

Buddhist Era Date

The bb Operator

Represents the year part of the date & time as per the Buddhist Era (BE) calendar in the shorthand number format (Ex. 52 for Buddhist year 2552 which is equivalent to Georgian calendar year 2009, the difference being 543 years.)

The bbb Operator

Represents the year part of the date & time as per the Buddhist Era (BE) calendar in the full format (Ex. 2552 would represent the year 2009 according to the Georgian calendar year)

Forced conversion to Georgian Calendar

The [$] Operator

To display dates using the Gregorian calendar, regardless of the Regional Options or Regional Settings of the Microsoft Windows Control Panel setting, type [$] before the date format code — for example, [$]dd/mm/yy.

You can download a sample worksheet with examples of custom format date and time in Excel here or click on the button below: