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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!