Concatenated date formatting

Lorraine Metler

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!

Genevieve P.

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

Prasad Kanse

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

Lorraine Metler

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!