Microsoft Office Forums Formula to convert dates into text/values
 Register FAQ Search Today's Posts Mark Forums Read

#1
06-30-2019, 02:45 AM
 Marcia Windows 7 32bit Office 2013 Expert Join Date: May 2018 Location: Philippines Posts: 443
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.
Attached Files
 Birthday.xlsx (8.6 KB, 4 views)
#2
06-30-2019, 06:06 AM
 p45cal Windows 10 Office 2016 Expert Join Date: Apr 2014 Posts: 528

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
07-01-2019, 06:53 AM
 Marcia Windows 7 32bit Office 2013 Expert Join Date: May 2018 Location: Philippines Posts: 443

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 Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post Glitch Excel Programming 3 08-03-2018 02:58 PM LyndaH Excel 4 03-26-2017 08:31 PM Lady18 Excel 5 09-10-2016 03:12 AM Lady18 Excel 1 09-10-2016 12:15 AM MYGS Excel 16 01-21-2013 08:18 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 02:13 AM.

 -- Default Style -- Lightweight -- New Mobile Contact Us - Privacy Statement - Top