Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

How to convert date to ordinal date format in Excel?

doc date to ordinal 1
As we all known, you can format date as multiple format in Excel, such as 1/1/2016, 1-Jan-16 and so on. However, have you ever tried to convert the date to ordinal date format as below screenshot shown? Actually, in Excel, there is no direct way to format date as ordinal date format. But do not worry, here this article, introduces the tricks on converting date to ordinal date format in Excel.

Convert date to ordinal date format with formula

Convert date to ordinal date format with Defined Function

Convert cardinal number to ordinal number with Kutools for Excel good idea3

Apply Date formatting (change date to multiple date formatting as you need)

doc apply date formatting

arrow blue right bubble Convert date to ordinal date format with formula


Here is a formula can help you convert date to ordinal.

Select a blank cell next to the date you want to convert to ordinal date format, C2 for instance, and enter this formula
=DAY(A2)&IF(OR(DAY(A2)={1,2,3,21,22,23,31}),CHOOSE(1*RIGHT(DAY(A2),1),"st","nd ","rd "),"th")&TEXT(A2,"mmmm, yyyy")
and then press Enter key, and drag the autofill handle over the cells you want to convert date to ordinal. See screenshot:
doc date to ordinal 2

Now the dates have been converted to ordinal dates.

Note: In the formula, A2 is the date you want to convert to ordinal date, you can change it as you need.

doc attention 1  Kutools for Excel's Auto Text, collect all formulas to a group as auto text, you can insert them anytime as you want whithout remembering! Free Download Now!

arrow blue right bubble Convert date to ordinal date format with Defined Function

Moreover, you also can apply Defined Function to convert standard date to ordinal date.

1. Press Alt + F11 keys to open Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste below code to the script.

Code: Convert date to ordinal

Function OrdinalDate(xDate As Date)
'UpdatebyExtendoffice20160705
    Dim xDay As Integer
    Dim xDayTxt As String
    Dim xMonth As Integer
    Dim xMonTxt As String
    Dim xYear As Long
    
    xDay = Day(xDate)
    xMonth = Month(xDate)
    xYear = Year(xDate)
    
    Select Case xDay
        Case 1: xDayTxt = "st"
        Case 2: xDayTxt = "nd"
        Case 3: xDayTxt = "rd"
        Case 21: xDayTxt = "st"
        Case 22: xDayTxt = "nd"
        Case 23: xDayTxt = "rd"
        Case 31: xDayTxt = "st"
        Case Else: xDayTxt = "th"
    End Select

    xMonTxt = Switch(xMonth = 1, " January", _
                     xMonth = 2, " February", _
                     xMonth = 3, " March", _
                     xMonth = 4, " April", _
                     xMonth = 5, " May", _
                     xMonth = 6, " June", _
                     xMonth = 7, " July", _
                     xMonth = 8, " August", _
                     xMonth = 9, " September", _
                     xMonth = 10, " October", _
                     xMonth = 11, " November", _
                     xMonth = 12, " December")
    OrdinalDate = xDay & xDayTxt & xMonTxt & " " & xYear
End Function

doc date to ordinal 3

3. Save the code and close the VBA window, go back to select a cell which will place the converted date, and enter this formula =OrdinalDate(J2), press Enter key, then drag fill handle overt the cells you need. See screenshot:
doc date to ordinal 4

Tip: in the formula, J2 is the date you want to convert to ordinal date, you can change as you need.


arrow blue right bubble Convert cardinal number to ordinal number with Kutools for Excel

In most of cases, you may want to convert numbers to ordinal numbers when you are ranking. In Excel, there is no built-in function can handle, but if you have Kutools for Excel – a handy add in tool, its Convert Number to Ordinal utility can do a favor for you.

Kutools for Excel, with more than 120 handy Excel functions, enhance your working efficiency and save your working time.

After free installing Kutools for Excel, please do as below:

Select the numbers you want to convert to ordinal, and click Kutools > Content > Convert Number to Ordinal. See screenshot:
doc date to ordinal 5

doc download 1


Recommended Productivity Tools for Excel

kte tab 201905

Kutools for Excel Helps You Always Finish Work Ahead of Time, and Stand Out From Crowd

  • More than 300 powerful advanced features, designed for 1500 work scenarios, increasing productivity by 70%, give you more time to take care of family and enjoy life.
  • No longer need memorizing formulas and VBA codes, give your brain a rest from now on.
  • Become an Excel expert in 3 minutes, Complicated and repeated operations can be done in seconds, 
  • Reduce thousands of keyboard & mouse operations every day, say goodbye to occupational diseases now.
  • 110,000 highly effective people and 300+ world-renowned companies' choice.
  • 60-day full features free trial. 60-day money back guarantees. 2 years of free upgrade and support.

Brings Tabbed Browsing and Editing to Microsoft Office, Far More Powerful Than The Browser's Tabs

  • Office Tab is designed for Word, Excel, PowerPoint and Other Office Applications: Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Phenox · 2 years ago
    Hello. Your VBA code works fine. I have one question
    can you tell me what I have do to get the first 9 numbers in a two-digit-look?
    1st to 01st, 2nd to 02nd aso.
    I have no idea from VBA

    thx in advance
    Phenox
    • To post as a guest, your comment is unpublished.
      Sunny · 1 years ago
      Sorry I did not understand your question clearly. You mean to convert first 9 numbers(123...9) to two digits(010203...09), not in date,only the numbers? Or convert the date such as 1/1/2001 to 01st Janaury 2001?