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:

=DAY(D2)&LOOKUP(DAY(D2),{1,"st";2,"nd";3,"rd";4,"th";21,"st";22,"nd";23,"rd";24,"th";31,"st"})

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)

Extra:

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

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

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

  1. 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

    Like

  2. Paul says:

    Is this possible in Word 2016?

    Like

  3. 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.

    Like

  4. 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?

    Like

  5. e.James says:

    Thank you! This was exactly what I needed. Note that you can simplify the formula as follows:
    =DAY(D2)&LOOKUP(MOD(DAY(D2),10),{1,”st”;2,”nd”;3,”rd”;4,”th”})

    Liked by 1 person

    • 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.

      Regards.

      Like

Comments, suggestions, corrections are welcome.

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

WordPress.com Logo

You are commenting using your WordPress.com 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