Ordinal Numbers with DatePicker

[roger.stern@mail.com]

Forgive me if I'm not using the correct terminology. I've created a date picker content control which displays a calendar for picking dates. I use the following format display: d of MMMM, yyyy





But I would like the day to be an ordinal number. What do I have to do to achieve that?


Thanks

[gmayor]

Unfortunately date content controls do not cater for ordinal dates, however, you can use the control to select the date, then using a macro change the control type to Rich Text and format the selected date as required. In the thisdocument module of the document or its template add the following. Then when you leave the field it will be converted to display the field as required. The three lines that add the superscript are optional.
Click into the changed field to select a new date.

Option Explicit
'Graham Mayor - https://www.gmayor.com - Last updated - 20 Sep 2023

Private Const sList As String = "0123456789"

Private Sub Document_ContentControlOnEnter(ByVal ContentControl As ContentControl)
Dim oRng As Range
Dim dDate As Date
    Select Case ContentControl.Title
        Case Is = "Date" 'The title of the date field
            If ContentControl.ShowingPlaceholderText = False Then
                If ContentControl.Type = wdContentControlRichText Then
                    Set oRng = ContentControl.Range
                    oRng.MoveStartWhile sList
                    oRng.End = oRng.Start + 5
                    oRng.Text = ""
                    Set oRng = ContentControl.Range
                    dDate = oRng.Text
                    ContentControl.Type = wdContentControlDate
                    oRng.Text = Format(dDate, "dd/MM/yyyy")
                End If
            End If
    End Select
End Sub

Private Sub Document_ContentControlOnExit(ByVal ContentControl As ContentControl, Cancel As Boolean)
Dim oRng As Range
Dim dDate As Date
    Select Case ContentControl.Title
        Case Is = "Date" 'The title of the date field
            If ContentControl.ShowingPlaceholderText = False Then
                If ContentControl.Type = wdContentControlDate Then
                    Set oRng = ContentControl.Range
                    dDate = CDate(oRng.Text)
                    ContentControl.Type = wdContentControlRichText
                    oRng.Text = Format(dDate, "d") & DateOrdinal(Format((dDate), "d")) & _
                        " of " & Format((dDate), "MMMM yyyy")
                    oRng.MoveStartWhile sList
                    oRng.End = oRng.Start + 2
                    oRng.Font.Superscript = True
                End If
            End If
    End Select
End Sub

Private Function DateOrdinal(Val As Long) As String
'Paul Edstein
Dim strOrd As String
    If (Val Mod 100) < 11 Or (Val Mod 100) > 13 Then strOrd = Choose(Val Mod 10, "st", "nd", "rd") & ""
    DateOrdinal = IIf(strOrd = "", "th", strOrd)
lbl_Exit:
    Exit Function
End Function

[roger.stern@mail.com]

Thank you very much

[roger.stern@mail.com]

I'm not an expert on vba and macros. Do I past the code as a module? How do I run it? And should the code dDate, "dd/MM/yyyy"be changed to my date format?
Thanks again,
Roger

[gmayor]

The code as posted goes in the ThisDocument module of the document, which should be saved as macro enabled. It runs automatically when you enter and leave the content control.
I will probably create a web page with a fuller explanation, but I am currently away from my office until at least December.

[roger.stern@mail.com]

Okay, Thank you, What are the three lines that make it superscript? Also, your line

"
oRng.Text = Format(dDate, "dd/MM/yyyy") Does that have to match the format of the date date picker? I use "dd of MM, yyy"
Does the macro have to be written that way?

[roger.stern@mail.com]

I think I did what you said, but it doesn't work

[Italophile]

Originally Posted by roger.stern@mail.com

You appear to be having difficulty understanding the term "OnExit". This refers to the insertion point leaving the content control. When the insertion point is moved from away from the content control the Document_ContentControlOnExit event code is triggered which then formats the date. See attached images.

[roger.stern@mail.com]

I'm sorry. I'm much less skilled then you ARE. I'm not getting what you're saying

[Guessed]

If you need help to do this, post a sample document so someone can see what you have actually done with the code and what elements of the document align with what the code is expecting to see.

[Italophile]

See attached example document

[gmayor]

I have modified the code to reflect the fact that you have two date fields, both now titled independently from one another. Select a date then click outside the content control.

[Italophile]

Originally Posted by gmayor

I have modified the code to reflect the fact that you have two date fields, both now titled independently from one another. Select a date then click outside the content control.

You're responding to the wrong person. I posted that file in an effort to help the OP understand how to use your code.

[gmaxey]

Here is a different version that doesn't absolutely require the exit event.

[Italophile]

Originally Posted by gmaxey

Here is a different version that doesn't absolutely require the exit event.

I'm not sure how providing a more complex solution without any explanation of how it is implemented will be helpful to the OP who, from the comments they have made, seems to already be struggling to understand the solution provided by gmayor.