Recurring Date to Fall on Specific Day
Hi -
I've set up two columns "Cadence" and "Launch Date". For the top row of my sheet, I've listed the start date of the month (i.e. November 2) under the Launch Date for formula purposes.
From there, on another row, I have a task where it should be completed 3 weeks after the start date of the month, but fall on a Friday.
The current formula =[Launch Date]1 + Cadence23 works well (and leads me to November 23), but the date falls on Tuesday, where I need it to fall on a Friday.
Any idea how to adjust the formula?
Thanks!
Jessica
Best Answer
-
Jessica,
Not exactly clear of your setup but if you wanted the end date always on Friday you would use the WEEKDAY formula.
The below is an example of this. This adds 3 weeks to a [Start Date] column then pushes it up to the coming Friday.
=IF(WEEKDAY([Start Date]@row + 21) = 7, [Start Date]@row + 27, [Start Date]@row + 21 + (6 - WEEKDAY([Start Date]@row)))
Answers
-
Jessica,
Not exactly clear of your setup but if you wanted the end date always on Friday you would use the WEEKDAY formula.
The below is an example of this. This adds 3 weeks to a [Start Date] column then pushes it up to the coming Friday.
=IF(WEEKDAY([Start Date]@row + 21) = 7, [Start Date]@row + 27, [Start Date]@row + 21 + (6 - WEEKDAY([Start Date]@row)))
-
That worked perfectly, THANK YOU Leibel!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!