Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Office 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Adding Ordinal Notation to Dates.

Adding Ordinal Notation to Dates

by Allen Wyatt
(last updated October 19, 2019)


When developing a workbook, you may have a need to place suffixes such as "st, nd, rd, or th" at the end of dates, as in "9th March." Unfortunately, there is no way to do this using the built-in date formats you can apply to individual cells. You can create custom formats for each of the four suffix types, if desired, but they would have to be applied individually based on the contents of the cell itself.

The only other option is to use some sort of conversion formula. These are easy enough to put together, but the resulting cell will not contain a true Excel date, but text. This precludes the cell contents from being used in other date-related functions. The following is an example of the type of conversion formula you can use:

CHOOSE(1*RIGHT(DAY(A1),1),"st","nd ","rd "),"th")
&TEXT(A1,"mmmm, yyyy")

There are others, but they all essentially do the same thing—pull the various parts of a date apart and put them back together with the proper suffix.

If you prefer, you can also create a macro function that would return a properly formatted date, with the ordinal suffix. The following is one such macro:

Function OrdinalDate(myDate As Date)
    Dim dDate As Integer
    Dim dText As String
    Dim mDate As Integer
    Dim mmmText As String

    dDate = Day(myDate)
    mDate = Month(myDate)

    Select Case dDate
        Case 1: dText = "st"
        Case 2: dText = "nd"
        Case 3: dText = "rd"
        Case 21: dText = "st"
        Case 22: dText = "nd"
        Case 23: dText = "rd"
        Case 31: dText = "st"
        Case Else: dText = "th"
    End Select

    Select Case mDate
        Case 1: mmmText = " January"
        Case 2: mmmText = " February"
        Case 3: mmmText = " March"
        Case 4: mmmText = " April"
        Case 5: mmmText = " May"
        Case 6: mmmText = " June"
        Case 7: mmmText = " July"
        Case 8: mmmText = " August"
        Case 9: mmmText = " September"
        Case 10: mmmText = " October"
        Case 11: mmmText = " November"
        Case 12: mmmText = " December"
    End Select

    OrdinalDate = dDate & dText & mmmText
End Function

You use the macro by simply invoking it within a cell formula. For example, if you have a date stored in cell B7, you can use the following in any other cell:



If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (10033) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Office 365. You can find a version of this tip for the older menu interface of Excel here: Adding Ordinal Notation to Dates.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...


Selecting Columns in VBA when Cells are Merged

If you have a macro that selects different columns in a worksheet while processing information, you may get some ...

Discover More

Forcing a Page Break Before a Paragraph

There are times that you just want to begin a paragraph (perhaps a heading) at the top of a new page. Word allows you to ...

Discover More

Controlling the Printer in a Macro

Need to access the advanced capabilities of a printer from within an Excel macro? You may be out of luck, unless you ...

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

More ExcelTips (ribbon)

Displaying a Number as Years and Months

How do you display a number of years, such as 3.67 years, as a number of years and months? It’s simple to do with a ...

Discover More

Converting European Dates to US Dates

Those in Europe use a date format that is different than those in the US; this is not news. But what if you need to ...

Discover More

Using Early Dates

Excel is brilliant at handling dates--as long as they aren't dates earlier than the base date used by the program. If you ...

Discover More

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.


If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is six more than 1?

2019-10-29 13:35:36

J. Woolley

@Jacob van Vaardegem

Here is a macro that implements your excellent suggestions (2019-10-19 and 2019-10-24) for ordinal dates by Conditional Format:

Public Sub OrdinalDateCF()
Dim msg As String, ans As String, fmt As String
Dim addr As String, expr As String
Dim n1 As Integer, n2 As Integer, nod As Integer
Const myName As String = "OrdinalDateCF"
Const quote As String = """"
msg = "To display ordinal dates in the Selection using a " _
& "Conditional Format, enter a date format using:" _
& vbNewLine & "mmmm for full month name," _
& vbNewLine & "mmm for abbreviated month," _
& vbNewLine & "mm for 2-digit month," _
& vbNewLine & "m for month number," _
& vbNewLine & "od for ordinal day number," _
& vbNewLine & "yyyy for year number," _
& vbNewLine & "yy for 2-digit year." _
& vbNewLine & "Include a time format if desired." _
& vbNewLine _
& vbNewLine & "Example ... Result" _
& vbNewLine & "od-mmm-yyyy ... 7th-Dec-1941" _
& vbNewLine & "mmmm od, yy ... June 6th, 44" _
& vbNewLine & "m/od/yyyy ... 9/11th/2001" _
& vbNewLine & "mmmm od ... December 31st" _
& vbNewLine & "od"" of ""mmmm ... 4th of July"
ans = InputBox(msg, myName, "mmmm od, yyyy")
If ans = vbNullString Then Exit Sub
n2 = 0
nod = InStr((n2 + 1), ans, "od")
If nod = 0 Then
msg = "Error: The date format must include" _
& vbNewLine & "od for ordinal day number."
MsgBox msg, vbCritical, myName
Exit Sub
End If
n1 = InStr((n2 + 1), ans, quote)
If (n1 > 0 And n1 < nod) Then ' ignore od within a quoted string
n2 = InStr((n1 + 1), ans, quote)
n2 = n1
End If
Loop Until (n2 = 0 Or n1 > nod Or n2 < nod)
msg = Left(ans, (nod - 1))
fmt = msg & Replace(ans, "od", "d""th""", nod, 1)
On Error Resume Next
Selection.NumberFormat = fmt
If Err <> 0 Then
msg = "Error: This date format is invalid." & vbNewLine & ans
MsgBox msg, vbCritical, myName
Exit Sub
End If
On Error GoTo 0
addr = Selection.Cells(1, 1).Address(False, False)
expr = "=OR(DAY(" & addr & ")=3,DAY(" & addr & ")=23)"
fmt = msg & Replace(ans, "od", "d""rd""", nod, 1)
Call OrdinalDateCF_Do(expr, fmt)
expr = "=OR(DAY(" & addr & ")=2,DAY(" & addr & ")=22)"
fmt = msg & Replace(ans, "od", "d""nd""", nod, 1)
Call OrdinalDateCF_Do(expr, fmt)
expr = "=OR(DAY(" & addr & ")=1,DAY(" & addr & ")=21,DAY(" & addr & ")=31)"
fmt = msg & Replace(ans, "od", "d""st""", nod, 1)
Call OrdinalDateCF_Do(expr, fmt)
End Sub

Private Sub OrdinalDateCF_Do(expr As String, fmt As String)
With Selection.FormatConditions.Add(xlExpression, , expr)
.NumberFormat = fmt
.StopIfTrue = True
End With
End Sub

2019-10-27 12:38:01

Willy Vanhaelen

To display automatically the day with suffix while preserving the ability to use the dates in calculations can be done by using a change event macro:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub
If Not IsDate(Target) Then Exit Sub
Dim Ord As String
Select Case Day(Target)
Case 1, 21, 31: Ord = "st"
Case 2, 22: Ord = "nd"
Case 3, 23: Ord = "rd"
Case Else: Ord = "th"
End Select
Target.NumberFormat = "d""" & Ord & """ mmmm yyyyy"
End Sub

This macro acts on column A.
Remember: this macro must reside in the code page of the sheet. Right click the sheet name and select View Code. That's the place to be.

2019-10-26 12:38:20

Peter Atherton

Conditional formatting
I had hoped to use somethng like the following for the formula
but alas Excel won't accept arrays in CF. The following will do the trick

st =OR(DAY(A1)=1,DAY(A1)=21,DAY(A1)=31)
nd =OR(DAY(A1)=2,DAY(A1)=22)
rd =OR(DAY(A1)=3,DAY(A1)=23)
th =NOT(OR(DAY(A1)=1,DAY(A1)=2,DAY(A1)=3,DAY(A1)=21,DAY(A1)=22,DAY(A1)=23,DAY(A1)=31))

Still, a nice hour passed thinking:-)

2019-10-24 09:33:09

Jacob van Vaardegem

Further to Peter's post

The macro is handy, but the downside is that if/when any of the dates change, you'll have to re-apply the macro (every time). Obviously you would want to link this to a button then.
With Conditional formatting the formats get applied automatically, irrespective of the (changed) values. And you can still sort and calculate with them.

If it is too much work to create those Conditional format rules manually, you can also combine your method with mine and create a macro that will apply a set of conditional formats to a desired range of cells. I haven't actually supplied that code here, but that can certainly be done.

2019-10-23 19:01:53

Peter Atherton

Further to Jacob van Gaardegem's post

I prefer this method to formatting dates as you do not loose the abiltity to sort or run calculations with the dates. Here, for anyone interested, is a macro to apply formats to selected dates.

Sub OrdDateFormat()
Dim c As Range, nDay As Integer
Dim nf1 As String, nf2 As String
Dim nf3 As String, nf4 As String
Dim bShowYear As Boolean

bShowYear = Application.InputBox("Show year in format?", "Select Format type", 0, Type:=2)
If bShowYear = 0 Then
nf1 = "d""th"" mmmm"
nf2 = "d""st"" mmmm"
nf3 = "d""nd"" mmmm"
nf4 = "d""rd"" mmmm"
nf1 = "d""th"" mmm yyyy"
nf2 = "d""st"" mmm yyyy"
nf3 = "d""nd"" mmm yyyy"
nf4 = "d""rd"" mmm yyyy"
End If

For Each c In Selection
If Not IsDate(c) Then
'Do Nothing
Select Case Day(c)
Case Is = 1, 21, 31
c.NumberFormat = nf2
Case Is = 2, 22
c.NumberFormat = nf3
Case Is = 3, 23
c.NumberFormat = nf4
Case Else
c.NumberFormat = nf1
End Select
End If
Next c

End Sub

2019-10-19 11:43:15

Willy Vanhaelen

Oops ! disregard my 2 previous UDF's. I have been a little hasty and didn't test them enough. Here is one that does the job correctly (still as short):

Function OrdinalDate(D As Date)
Dim Ord As String
Select Case Day(D)
Case 1, 21, 31: Ord = "st "
Case 2, 22: Ord = "nd "
Case 3, 23: Ord = "rd "
Case Else: Ord = "th "
End Select
OrdinalDate = Day(D) & Ord & Format(D, "mmmm")
End Function

2019-10-19 10:59:18

Jacob van Vaardegem

If you don't want to convert the real dates into texts, and still want to be able to calculate with them, you can use Conditional Formatting to achieve the same result.
Select the range of dates you want to apply this to (and let's say the range starts in A1), and apply the normal formatting using Custom format
d"th" mmmm
This will turn all dates into something like '1th October', '2th October', etc.

Then, on the same range, apply 3 Conditional Formatting rules:
=OR(DAY(A1)=1,DAY(A1)=21,DAY(A1)=31) > for this rule set the format to Custom format d"st" mmmm
=OR(DAY(A1)=2,DAY(A1)=22) > for this rule set the format to Custom format d"nd" mmmm
=OR(DAY(A1)=3,DAY(A1)=23) > for this rule set the format to Custom format d"rd" mmmm
For each rule, set 'Stop if True'.

In this way you can just accommodate any date with the right format and can still enter them or calculate with them as normal dates.

Obviously you can copy this format, including the conditional formatting, to any other range you want to apply Ordinal Formatting for.

2019-10-19 08:42:26

Willy Vanhaelen

Here is an even shorter version:

Function OrdinalDate(D As Date)
Dim Ord As String
Ord = "th "
On Error Resume Next
Ord = Choose(Right(Day(D), 1), "st ", "nd ", "rd ")
OrdinalDate = Day(D) & Ord & Format(D, "mmmm")
End Function

2019-10-19 08:22:56

Willy Vanhaelen

The macro in this tip can be drastically shortened. Here is my simplified version:

Function OrdinalDate(D As Date)
Dim Ord As String
Select Case Right(Day(D), 1)
Case 1: Ord = "st "
Case 2: Ord = "nd "
Case 3: Ord = "rd "
Case Else: Ord = "th "
End Select
OrdinalDate = Day(D) & Ord & Format(D, "mmmm")
End Function

This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.