Mail Merge Ordinal Date Format (Superscript)

[nancyching1711]

Hi

I googled but could not find a solution to the date format for mail merge.

How do I merge the date (1-Apr-2018) into the letter in ordinal date format with superscript, ie 1^st April 2018.

Thank you for the assistance.

Regards
Nancy

[Sam Capricci]

this might work...
=DAY(A2)&LOOKUP(DAY(A2),{1,2,3,4,21,22,23,24,31;"st","nd","rd","th","st","nd","rd","th","st"})&" "&LOOKUP(MONTH(A2),{1,2,3,4,5,6,7,8,9,10,11,12;"January","February","March","April","May","June","July","August","September","October","November","December"})&" "&YEAR(A2)

assumes the data is in A2, adjust as needed.

[nancyching1711]

Hi

Thank you for assisting with the formula to convert the date to ordinal date format.

However, please assist with
1) the superscript format, ie 1^st April 2018
2) upon mail merge, retaining the ordinal date format with superscript.

Thank you and regards.

[Norie]

Nancy

Where are you mail merging to?

If it's Word you can add the date field twice, the first field would show the day as ordinal using a switch and the second only the month and year.

In Word ordinal numbers should automatically appear as superscript.

[nancyching1711]

Hi


I tried this code:

{QUOTE{SET MergeDate {MERGEFIELD Date}}{MergeDate \@ "d"}{IF{(MOD({=({MergeDate \@ d}+89},100)>2)*(MOD({={MergeDate \@ d}+9},10)<3)}= 1 {=MOD({MergeDate \@ d},10)-2 \# rd;st;nd} th}{MergeDate \@ "' MMMM yyyy"}}

It works for 1^st ,2^nd and 3^rd. However, 4 onwards showed the superscript of "rd" instead of "th".

Kindly help to identify the error.

Attached files for your kind assistance.

Thank you.

[nancyching1711]

Hi

With reference to my above post, is it that excel is unable to mail merge ordinal date format with superscript font.

Kindly assist.

Thank you.

Regards
Nancy

[Norie]

You can use this to get the ordinal, still trying to work out the superscript part.

{ MERGEFIELD Date \@ "d" \* Ordinal } {MERGEFIELD Date \@ "MMMM yyyy"}

[nancyching1711]

Hi Norie

Thank you for the code. The superscript part is giving me much headache.

Regards
Nancy

[Norie]

Nancy

You could try doing it manually by selecting the th/nd/rd part and applying the appropriate formatting.

Not sure how/if that will hold up when doing a mail merge though.

[nancyching1711]

Hi Norie

That's what I am doing now. Merging then changing the font type for 2500 is really time consuming. I googled for code and able to get 1st-3rd to superscript format but 4th onwards return with rd eg 4rd, 5rd etc.

However, I do not know how to trouble shoot for error.

Please assist. Thank you.