Date Format to letter


Can someone please help me in converting date to format Friday, November 27th ?

Thanks in advance.


Hi @pparinita,
Use now.ToLongDateString.


You can simply format the date using “dddd” for the day of week and “MMMM” for month.

Cdate("11/27/2018").ToString("dddd, MMMM d")

So that takes a datetime value Cdate("11/27/2018") or if it’s already a datetime value then you don’t need the cdate(), and formats it. However, the return will not include the “th” and “st” etc

In order to add the suffix to the day, you will need to use a trick I believe.
Let’s just store all the suffixes into an array, like so:

dateSuffixes = {"st","nd","rd","th","th","th","th","th","th","th","th","th","th","th","th","th","th","th","th","th","st","nd","rd","th","th","th","th","th","th","th","st"}

Then, we can use the day numeric value as the index and concatenate it to the day.

For example,

Cdate("11/27/2018").ToString("dddd, MMMM d"""+dateSuffixes(Cdate("11/27/2018").Day-1)+"""")

I added embedded quotations around the suffix or otherwise it won’t work.
And, you can replace the date string with your variable that contains the date string, or if it is already a datetime variable, then replace CDate() and the date string with the datetime variable.

EDIT: here is example using Now as datetime:

Now.ToString("dddd, MMMM d"""+dateSuffixes(Now.Day-1)+"""")

EDIT2: correction: minus 1 instead of plus 1 when taking the index in dateSuffixes

I hope this is the answer you were looking for.