Concatenate with Date Formatting
Can someone help me with this formula? I'm getting an error and haven't been able to figure out the issue. Here is the format I'm looking for:
Customer Legal Name.Letter Type.Day Month Year
=[Customer Legal Name]@row + "." + [Letter Type]@row + "." + DAY([Send Letter Date]@row + " " + IF(MONTH([Send Letter Date]@row) = 1, "January ", IF(MONTH([Send Letter Date]@row) = 2, "February ", IF(MONTH([Send Letter Date]@row) = 3, "March ", IF(MONTH([Send Letter Date]@row) = 4, "April ", IF(MONTH([Send Letter Date]@row) = 5, "May ", IF(MONTH([Send Letter Date]@row) = 6, "June ", IF(MONTH([Send Letter Date]@row) = 7, "July ", IF(MONTH([Send Letter Date]@row) = 8, "August ", IF(MONTH([Send Letter Date]@row) = 9, "September ", IF(MONTH([Send Letter Date]@row) = 10, "October ", IF(MONTH([Send Letter Date]@row) = 11, "November ", IF(MONTH([Send Letter Date]@row) = 12, "December ")))))))))))) + " " + YEAR([Send Letter Date]@row))))
Thank you in advance!
Best Answer
-
It is because you have nothing in the Date field. Start the whole thing off with an IF statement to only run it if the date field has been filled in.
=IF([Send Letter Date]@row <> "", [Customer Legal Name]@row + "." + [Letter Type]@row + "." + DAY([Send Letter Date]@row) + " " + IF(MONTH([Send Letter Date]@row) = 1, "January ", IF(MONTH([Send Letter ...................................YEAR([Send Letter Date]@row))
Answers
-
Remove a closing parenthesis from the end and use it to close out the DAY function. Then you are going to need to completely remove two more closing parenthesis from the very end. The formula should end with just a single closing parenthesis.
-
The system is still adding another ) at the end. This is what I ended up with. Any thoughts?
=[Customer Legal Name]@row + "." + [Letter Type]@row + "." + DAY([Send Letter Date]@row) + " " + IFERROR(IF(MONTH([Send Letter Date]@row) = 1, "January ", IF(MONTH([Send Letter Date]@row) = 2, "February ", IF(MONTH([Send Letter Date]@row) = 3, "March ", IF(MONTH([Send Letter Date]@row) = 4, "April ", IF(MONTH([Send Letter Date]@row) = 5, "May ", IF(MONTH([Send Letter Date]@row) = 6, "June ", IF(MONTH([Send Letter Date]@row) = 7, "July ", IF(MONTH([Send Letter Date]@row) = 8, "August ", IF(MONTH([Send Letter Date]@row) = 9, "September ", IF(MONTH([Send Letter Date]@row) = 10, "October ", IF(MONTH([Send Letter Date]@row) = 11, "November ", IF(MONTH([Send Letter Date]@row) = 12, "December ")))))))))))) + " " + YEAR([Send Letter Date]@row))
-
Yes. What is the IFERROR for exactly?
-
My bad...that was an error. Here's the corrected version. I'm getting an "Invalid Operation" error.
=[Customer Legal Name]@row + "." + [Letter Type]@row + "." + DAY([Send Letter Date]@row) + " " + IF(MONTH([Send Letter Date]@row) = 1, "January ", IF(MONTH([Send Letter Date]@row) = 2, "February ", IF(MONTH([Send Letter Date]@row) = 3, "March ", IF(MONTH([Send Letter Date]@row) = 4, "April ", IF(MONTH([Send Letter Date]@row) = 5, "May ", IF(MONTH([Send Letter Date]@row) = 6, "June ", IF(MONTH([Send Letter Date]@row) = 7, "July ", IF(MONTH([Send Letter Date]@row) = 8, "August ", IF(MONTH([Send Letter Date]@row) = 9, "September ", IF(MONTH([Send Letter Date]@row) = 10, "October ", IF(MONTH([Send Letter Date]@row) = 11, "November ", IF(MONTH([Send Letter Date]@row) = 12, "December ")))))))))))) + " " + YEAR([Send Letter Date]@row)
-
Have you confirmed that the [Send Letter Date] field is in fact set as a date type and houses actual dates?
-
Yes, I have.
-
Can you provide some screenshots?
-
Let me know if anything else will help.
-
It is because you have nothing in the Date field. Start the whole thing off with an IF statement to only run it if the date field has been filled in.
=IF([Send Letter Date]@row <> "", [Customer Legal Name]@row + "." + [Letter Type]@row + "." + DAY([Send Letter Date]@row) + " " + IF(MONTH([Send Letter Date]@row) = 1, "January ", IF(MONTH([Send Letter ...................................YEAR([Send Letter Date]@row))
-
You are AWESOME...THANK YOU!!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!