#1




Formula to convert dates into text/values
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. 
#2




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 11Aug1905 and 18Mar1908) 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,"@"))) 
#3




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.

Thread Tools  
Display Modes  

Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
Concatenate to reference values with that are dates  Glitch  Excel Programming  3  08032018 02:58 PM 
IF formula when some values are text & others $ amounts  LyndaH  Excel  4  03262017 08:31 PM 
How to convert VLOOKUP values to text on another sheet AUTOMATICALLY  Lady18  Excel  5  09102016 03:12 AM 
How to convert VLOOKUP values to text on another sheet AUTOMATICALLY  Lady18  Excel  1  09102016 12:15 AM 
Convert Formula Result to Static Text  MYGS  Excel  16  01212013 08:18 AM 