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:
- If m or mm is preceded by h or (Ex. yyyy-h-m) or
- 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:
[…] article dealt with the number and text custom format in Excel. Read more about custom format feature with date and time in Excel here. Editor’s […]
September 7th, 2009 at 12:59 am