#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 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,"@"))) |
#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 | 08-03-2018 02:58 PM |
IF formula when some values are text & others $ amounts | LyndaH | Excel | 4 | 03-26-2017 08:31 PM |
How to convert VLOOKUP values to text on another sheet AUTOMATICALLY | Lady18 | Excel | 5 | 09-10-2016 03:12 AM |
How to convert VLOOKUP values to text on another sheet AUTOMATICALLY | Lady18 | Excel | 1 | 09-10-2016 12:15 AM |
Convert Formula Result to Static Text | MYGS | Excel | 16 | 01-21-2013 08:18 AM |