Concatenated date formatting

Hi. I am trying to change the formatting of concatenated start and end dates. The formula is =[Program start date]@row + " - " + [Program end date]@row and it is returning the result as 02/16/22-02/17/22. I would like the formatting to read ddd-dd-mmm - ddd-dd-mmm or Wed-16-Feb - Thu-17-Feb. Is it possible to change the formatting in concatenated dates? Thank you!

Answers

  • Hi @Lorraine Metler

    Yes, it's possible, but it will end up being quite a big formula. The reason is that the formula will need to take into account every single possibility.

    1. First, we would write a statement that checks the day of the week. This means there are 7 possible days with 7 different text outcomes.
    2. Then we add the Day.
    3. Then we need to write another statement for every possible month (12) with each different text outcome (12).
    4. Finally, we do this same process all over again for the second date.


    Day of the Week

    =IF(WEEKDAY([Program start date]@row) = 1, "Sun", IF(WEEKDAY([Program start date]@row) = 2, "Mon", IF(WEEKDAY([Program start date]@row) = 3, "Tue", IF(WEEKDAY([Program start date]@row) = 4, "Wed", IF(WEEKDAY([Program start date]@row) = 5, "Thu", IF(WEEKDAY([Program start date]@row) = 6, "Fri", IF(WEEKDAY([Program start date]@row) = 7, "Sat")))))))

    then we add the dash

    + "-"

    Add the Day

    + DAY([Program start date]@row)

    add the dash

    + "-"

    Add the Month

    + IF(MONTH([Program start date]@row) = 1, "Jan", IF(MONTH([Program start date]@row) = 2, "Feb", IF(MONTH([Program start date]@row) = 3, "Mar", IF(MONTH([Program start date]@row) = 4, "Apr", IF(MONTH([Program start date]@row) = 5, "May", IF(MONTH([Program start date]@row) = 6, "Jun", IF(MONTH([Program start date]@row) = 7, "Jul", IF(MONTH([Program start date]@row) = 8, "Aug", IF(MONTH([Program start date]@row) = 9, "Sep", IF(MONTH([Program start date]@row) = 10, "Oct", IF(MONTH([Program start date]@row) = 11, "Nov", IF(MONTH([Program start date]@row) = 12, "Dec"))))))))))))


    Full Formula, including the second date:

    =IF(WEEKDAY([Program start date]@row) = 1, "Sun", IF(WEEKDAY([Program start date]@row) = 2, "Mon", IF(WEEKDAY([Program start date]@row) = 3, "Tue", IF(WEEKDAY([Program start date]@row) = 4, "Wed", IF(WEEKDAY([Program start date]@row) = 5, "Thu", IF(WEEKDAY([Program start date]@row) = 6, "Fri", IF(WEEKDAY([Program start date]@row) = 7, "Sat"))))))) + "-" + DAY([Program start date]@row) + "-" + IF(MONTH([Program start date]@row) = 1, "Jan", IF(MONTH([Program start date]@row) = 2, "Feb", IF(MONTH([Program start date]@row) = 3, "Mar", IF(MONTH([Program start date]@row) = 4, "Apr", IF(MONTH([Program start date]@row) = 5, "May", IF(MONTH([Program start date]@row) = 6, "Jun", IF(MONTH([Program start date]@row) = 7, "Jul", IF(MONTH([Program start date]@row) = 8, "Aug", IF(MONTH([Program start date]@row) = 9, "Sep", IF(MONTH([Program start date]@row) = 10, "Oct", IF(MONTH([Program start date]@row) = 11, "Nov", IF(MONTH([Program start date]@row) = 12, "Dec")))))))))))) + " - " + IF(WEEKDAY([Program end date]@row) = 1, "Sun", IF(WEEKDAY([Program end date]@row) = 2, "Mon", IF(WEEKDAY([Program end date]@row) = 3, "Tue", IF(WEEKDAY([Program end date]@row) = 4, "Wed", IF(WEEKDAY([Program end date]@row) = 5, "Thu", IF(WEEKDAY([Program end date]@row) = 6, "Fri", IF(WEEKDAY([Program end date]@row) = 7, "Sat"))))))) + "-" + DAY([Program end date]@row) + "-" + IF(MONTH([Program end date]@row) = 1, "Jan", IF(MONTH([Program end date]@row) = 2, "Feb", IF(MONTH([Program end date]@row) = 3, "Mar", IF(MONTH([Program end date]@row) = 4, "Apr", IF(MONTH([Program end date]@row) = 5, "May", IF(MONTH([Program end date]@row) = 6, "Jun", IF(MONTH([Program end date]@row) = 7, "Jul", IF(MONTH([Program end date]@row) = 8, "Aug", IF(MONTH([Program end date]@row) = 9, "Sep", IF(MONTH([Program end date]@row) = 10, "Oct", IF(MONTH([Program end date]@row) = 11, "Nov", IF(MONTH([Program end date]@row) = 12, "Dec"))))))))))))



    Let me know if this makes sense!

    Cheers,

    Genevieve

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

  • Hi Lorraine,

    The current solution that I have is a bit lengthy, but can help to fulfil your need.

    In order to convert the date formatting:

    Create 3 columns next to your Program start date and Program end date respectively

    Day: =IFERROR(IF(WEEKDAY([Past Due]@row) = 1, "Sun", IF(WEEKDAY([Past Due]@row) = 2, "Mon", IF(WEEKDAY([Past Due]@row) = 3, "Tue", IF(WEEKDAY([Past Due]@row) = 4, "Wed", IF(WEEKDAY([Past Due]@row) = 5, "Thu", IF(WEEKDAY([Past Due]@row) = 6, "Fri", IF(WEEKDAY([Past Due]@row) = 7, "Sat"))))))), "NA")

    Date: =DAY([Past Due]@row)

    Month: =IFERROR(IF(MONTH([Past Due]@row) = 1, "Jan", IF(MONTH([Past Due]@row) = 2, "Feb", IF(MONTH([Past Due]@row) = 3, "Mar", IF(MONTH([Past Due]@row) = 4, "Apr", IF(MONTH([Past Due]@row) = 5, "May", IF(MONTH([Past Due]@row) = 6, "Jun", IF(MONTH([Past Due]@row) = 7, "Jul", IF(MONTH([Past Due]@row) = 8, "Aug", IF(MONTH([Past Due]@row) = 9, "Sep", IF(MONTH([Past Due]@row) = 10, "Oct", IF(MONTH([Past Due]@row) = 11, "Nov", IF(MONTH([Past Due]@row) = 12, "Dec")))))))))))), "NA")

    Then combine the column value using: =Day@row + "-" + Date@row + "-" + Month@row

    In your case:

    Program start date: =Day@row + "-" + Date@row + "-" + Month@row

    Program end date: =Day@row + "-" + Date@row + "-" + Month@row

    For output of =[Program start date]@row + " - " + [Program end date]@row

    =Day@row + "-" + Date@row + "-" + Month@row + "-" + Day@row + "-" + Date@row + "-" + Month@row

  • Thank you both for your input into this! Very lengthy formulas for what I thought might be a quick answer😮 Working on this solution now using both of your suggestions. Again, I appreciate your time and input into this!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!