Date format Formula

Susan van Niekerk

Hi All

My Date column is formatted to YY/MM/DD - 2024-11-08

I have a week-ending column with the following formula. Returning the End day of the week that the Date column falls into

=IF(OR(ISBLANK([Date]@row), [Date]@row = "N/A"), "", "WE- " + ([Date]@row + (6 - WEEKDAY([Date]@row))))

BUT the return looks like this WE- 08-11-2024

How can I get it to return WE - 2024-11-08

Carson Penticuff

Give this a try:

=IF(OR(ISBLANK([Date]@row), [Date]@row = "N/A"), "", "WE - " + YEAR([Date]@row + (6 - WEEKDAY([Date]@row))) + "-" + IF(MONTH([Date]@row + (6 - WEEKDAY([Date]@row))) < 10, "0") + MONTH([Date]@row + (6 - WEEKDAY([Date]@row))) + "-" + IF(DAY([Date]@row + (6 - WEEKDAY([Date]@row))) < 10, "0") + DAY([Date]@row + (6 - WEEKDAY([Date]@row))))

This will essentially systematically assemble the desired date format. There are provisions for days and months that are single digit. Otherwise you would end up with results such as WE - 2025-1-4 instead of WE - 2025-01-04.

Carson Penticuff

Give this a try:

=IF(OR(ISBLANK([Date]@row), [Date]@row = "N/A"), "", "WE - " + YEAR([Date]@row + (6 - WEEKDAY([Date]@row))) + "-" + IF(MONTH([Date]@row + (6 - WEEKDAY([Date]@row))) < 10, "0") + MONTH([Date]@row + (6 - WEEKDAY([Date]@row))) + "-" + IF(DAY([Date]@row + (6 - WEEKDAY([Date]@row))) < 10, "0") + DAY([Date]@row + (6 - WEEKDAY([Date]@row))))

This will essentially systematically assemble the desired date format. There are provisions for days and months that are single digit. Otherwise you would end up with results such as WE - 2025-1-4 instead of WE - 2025-01-04.

Susan van Niekerk

Thank you Carson - Worked a treat :-)