Date Formats – A trick to format date with “st”, “nd”, “rd”, “th”

Excel Tips - Date Format

Well, there is no such custom format for date in Excel… However it can be achieved indirectly, with helper cellūüôā

Let’s take a look at the available date formats in Excel:

Excel Tips - Date Format 1

As shown above, there are many different “numeric” and “textual” expressions for

  • Day (e.g. 1 or 01)
  • Day of a week (e.g. Mon, or Monday)
  • Month (e.g. 1, 01, Jan, January, J)
  • Year (14, or 2014)

By arranging them with your favorite¬†separator (e.g. space, comma, hyphen, forward slash or even a dot) in your desired order, various date formats can be customized. ¬†For instance, if you format 1/1/2014 as “dddd, mmmm dd, yyyy” will give you “Wednesday, January 01, 2014” as a result. ¬†However there is no such format for “1st of January, 2014”.

The wonderful¬†thing about Excel is there would be¬†often (if not always) an alternative way to achieve what you want. ¬†Isn’t it?

Suppose we have the date (say 1/11/2014) in D2, we may apply the following custom format to it:

  • Format:¬†mmmm, yyyy
  • Result: November, 2014¬†

Excel Tips - Date Format 4   ==>  Excel Tips - Date Format 4

The trick now is to create day (with corresponding suffix “st”, “nd”, “rd”, “th”) in the helper cell (C2) by¬†the following formula:


The formula does the following:

  • Day(D2) – returns the day of the month in number
  • LOOKUP(DAY(D2),{1,”st”;2,”nd”;3,”rd”;4,”th”;21,”st”;22,”nd”;23,”rd”;24,”th”;31,”st”}) – returns the corresponding suffix of the day

This can be understood easily if we visualize the {green part} into a table.  Excel Tips - Date Format 5

CONCATENATE them, we will¬†have “1st”, “2nd”, “3rd”, “4-20th”, “21st”, “22nd”, “23rd”, “24-30th”, or “31st” according to the date value we have. ¬†So, here we go:

Excel Tips - Date Format 6

Tips: If you wish to have “1st of November, 2014” instead, apply the following custom format in D2:¬†“of” mmmm, yyyy ¬†(the double quotation mark is required)


The mmmmm (m x 5) is a less well-known format for Month.  It formats the month into J, F, M, A, M, J, J, A, S, O, N, D for from January to December respectively.

The benefit of it: SPACE.  It could be helpful in dashboard report or mini chart where real estate is expensive.

By the way, I have no idea why there is no such format for day of week… ¬†@_@

This entry was posted in Excel Tips, Formula and tagged , , , . Bookmark the permalink.

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s