Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 06-30-2019, 02:45 AM
Marcia's Avatar
Marcia Marcia is offline Formula to convert dates into text/values Windows 7 32bit Formula to convert dates into text/values Office 2013
Expert
Formula to convert dates into text/values
 
Join Date: May 2018
Location: Philippines
Posts: 443
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default 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
File Type: xlsx Birthday.xlsx (8.6 KB, 4 views)
Reply With Quote
  #2  
Old 06-30-2019, 06:06 AM
p45cal p45cal is offline Formula to convert dates into text/values Windows 10 Formula to convert dates into text/values Office 2016
Expert
 
Join Date: Apr 2014
Posts: 528
p45cal is a splendid one to beholdp45cal is a splendid one to beholdp45cal is a splendid one to beholdp45cal is a splendid one to beholdp45cal is a splendid one to beholdp45cal is a splendid one to beholdp45cal is a splendid one to behold
Default

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,"@")))
Reply With Quote
  #3  
Old 07-01-2019, 06:53 AM
Marcia's Avatar
Marcia Marcia is offline Formula to convert dates into text/values Windows 7 32bit Formula to convert dates into text/values Office 2013
Expert
Formula to convert dates into text/values
 
Join Date: May 2018
Location: Philippines
Posts: 443
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

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.
Reply With Quote
Reply

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

Other Forums: Access Forums

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


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2021, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2021 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft