Date format Formula
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
Best Answer
-
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.
Answers
-
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.
-
Thank you Carson - Worked a treat :-)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 462 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!