Hello. In a mail merge with Date of Birth (Col A) in Excel as its data source, the switch \@ "MMMM d, yyyy" doesn't work because the Date of Birth column contains data that have month and year or year only. What I did was to add a helper column (B) with the following formula:
=TEXT(A2,"MMMM d, yyyy")
Column B was formatted to text. I couldn't simply use =A2 because the text format returns the serial number of the dates. I would like a formula in B2 that when copied down, the incomplete dates in column A are returned as they are but if the date is complete with month day and year, return the date in text format. Column B will be the data source of the mail merge. Thank you.

Attached Files

Birthday.xlsx (8.6 KB, 10 views)

If the likes of 1940 are, as in your sheet, text then:
=IF(ISNUMBER(A2),TEXT(A2,"MMMM d, yyyy"),TEXT(A2,"@"))
?


If these aren't text, then 1940 gets converted to 23 April 1905, if this is out of scope for your possible dates you could set a limit say 2050 or 3000 (respectively equivalent to 11-Aug-1905 and 18-Mar-1908) and test if it's above that limit:
=IF(AND(ISNUMBER(A2),VALUE(A2)>2050),TEXT(A2,"MMMM d, yyyy"),TEXT(A2,"@"))
Problem now might be that a blank cell gets converted to a textual zero which can be circumvented with:
=IF(AND(ISNUMBER(A2),VALUE(A2)>2050),TEXT(A2,"MMMM d, yyyy"),IF(TEXT(A2,"@")="0","",TEXT(A2,"@")))

Thank you for giving three options, from which I chose formula number 1. The second and third formula compute Dec 1937 as 13850 even if it is formatted as text.