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))

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    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.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Shannon Heward
    Shannon Heward ✭✭✭✭

    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))

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Yes. What is the IFERROR for exactly?

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Shannon Heward
    Shannon Heward ✭✭✭✭

    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)

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Have you confirmed that the [Send Letter Date] field is in fact set as a date type and houses actual dates?

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Can you provide some screenshots?

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Shannon Heward
    Shannon Heward ✭✭✭✭

    Let me know if anything else will help.




  • 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))

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Shannon Heward
    Shannon Heward ✭✭✭✭

    You are AWESOME...THANK YOU!!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help. 👍️

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!