LinkBack URL
About LinkBacks
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 1st April 2018.
Thank you for the assistance.
Regards
Nancy
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 1st April 2018.
Thank you for the assistance.
Regards
Nancy
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.
Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
Sam Capricci
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 1st April 2018
2) upon mail merge, retaining the ordinal date format with superscript.
Thank you and regards.
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.
If posting code please use code tags, see here.
Hi
I tried this code:
It works for 1st ,2nd and 3rd. However, 4 onwards showed the superscript of "rd" instead of "th".
Please Login or Register to view this content.
Kindly help to identify the error.
Attached files for your kind assistance.
Thank you.
Last edited by nancyching1711; 04-25-2018 at 11:44 AM.
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
Last edited by nancyching1711; 04-29-2018 at 08:59 AM.
You can use this to get the ordinal, still trying to work out the superscript part.
{ MERGEFIELD Date \@ "d" \* Ordinal } {MERGEFIELD Date \@ "MMMM yyyy"}
Hi Norie
Thank you for the code. The superscript part is giving me much headache.
Regards
Nancy
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.
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks