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 🙂

Power BI Webinar

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…  @_@

Free Excel Dashboard Webinar


About MF

An Excel nerd who just transition into a role related to data analytics at current company......😊 Recently in love with Power Query and Power BI.😍 Keep learning new Excel and Power BI stuffs and be amazed by all the new discoveries.
This entry was posted in Excel Tips, Formula and tagged , , , . Bookmark the permalink.

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

  1. NumbGnat says:

    Thank you so much for this solution! Helped me a bunch!

    Liked by 1 person

  2. Pete Mallam says:

    This works better:

    =DAY(D2)&VLOOKUP(MOD(DAY(D2),10),{0,”th”;1,”st”;2,”nd”;3,”rd”;4,”th”},2)&” “&LOOKUP(MONTH(D2),{1,”January”;2,”February”………})


    • MF says:

      Hi Pete,
      Nice use of MOD. However by using MOD, it gives 11st, 12nd, and 13rd while the correct expression should be 11th, 12th, and 13th.
      For the latter part of the formula, it will be easier to use TEXT(D2, “MMMM, YYYY”).


  3. Isaac says:

    This should make the trick:

    =IF(ISNA(VLOOKUP(MOD(DAY(D2),10),{1,”st”;2,”nd”;3,”rd”;4,”th”},2))=TRUE,”th”, VLOOKUP(MOD(DAY(D2),10),{1,”st”;2,”nd”;3,”rd”;4,”th”},2))


  4. Vinay says:

    How is the reverse possible? I mean if I have a column with dates as “1st November, 2017” how can I get excel to understand so I can apply formulas like =DAYS() etc


  5. Paul says:

    Is this possible in Word 2016?


  6. Hoseiyn Suleiman says:

    Thanks for the formua, i added some more to it.

    =CONCATENATE(DAY(C2)&LOOKUP(DAY(C2),{1,”st”;2,”nd”;3,”rd”;4,”th”;21,”st”;22,”nd”;23,”rd”;24,”th”;31,”st”}),” “,TEXT(C2,”mmmm, yyyy”))

    Started with =CONCATENATE to merge the results from =DAY(C2)&LOOKUP(DAY(C2),{1,”st”;2,”nd”;3,”rd”;4,”th”;21,”st”;22,”nd”;23,”rd”;24,”th”;31,”st”}), ” ” – For spaces in the initial result and with the =TEXT(C2,”mmmm, yyyy”) where C2 is where my DATE is.

    C2 = 7/2/2017
    DAY(C2)&LOOKUP(DAY(C2),{1,”st”;2,”nd”;3,”rd”;4,”th”;21,”st”;22,”nd”;23,”rd”;24,”th”;31,”st”}) = 7th
    ” ” = {Spaces in between 7th[]February, 2017} — To make the final result much neater

    TEXT(C2, “mmmm, yyyy”) = February, 2017

    Complete result

    7th February, 2017

    Achieved all using one cell.


  7. MF says:

    Hi e.James
    Glad it helps.
    To my understanding , 11th , 12th and 13th are the correct expression. You formula may give you 11st, 12nd, 13rd… right?


  8. e.James says:

    Thank you! This was exactly what I needed. Note that you can simplify the formula as follows:


    • Anupam says:

      Thanks @e.James for further shortening the formula. However, it still misses the “10th”, “20th” and “30th”.
      Please can you revisit this issue and find a way out of this.



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 )

Facebook photo

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

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.