Calculated date that doesn't fall on weekend but next week day
I have a date field (Date to be Completed) where I calculate so many days before or after a "Course Launch Date". I need to make sure that if that calculation lands on a weekend it automatically advances to Monday. Here's the steps, just can't figure out the formula.
Course Launch Date: (PARENT): 5/26/20
Date to be Completed: =(PARENT([Course Launch Date]@row) + 12)
This formula results in a Sunday: How can I make it land on Monday if it's on a weekend? Or if needed - Land on Friday if it's Saturday and land on Monday if it's Sunday?
Thanks!!!
Best Answer
-
You can use an IF statement for this along the lines of...
=date formula + IF(WEEKDAY(date formula) = 7, -1, IF(WEEKDAY(date formula) = 1, 1))
=PARENT([Course Launch Date]@row) + 12 + IF(WEEKDAY(PARENT([Course Launch Date]@row) + 12) = 7, -1, IF(WEEKDAY(PARENT([Course Launch Date]@row) + 12) = 1, 1))
Answers
-
You can use an IF statement for this along the lines of...
=date formula + IF(WEEKDAY(date formula) = 7, -1, IF(WEEKDAY(date formula) = 1, 1))
=PARENT([Course Launch Date]@row) + 12 + IF(WEEKDAY(PARENT([Course Launch Date]@row) + 12) = 7, -1, IF(WEEKDAY(PARENT([Course Launch Date]@row) + 12) = 1, 1))
-
Yes!!! Thanks so much. Don't know why I couldn't come up with that formula.
Thanks so much!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- 10.7K Get Help
- 63 Global Discussions
- 69 Industry Talk
- 385 Announcements
- 3.5K Ideas & Feature Requests
- 55 Brandfolder
- 125 Just for fun
- 50 Community Job Board
- 464 Show & Tell
- 40 Member Spotlight
- 44 Power Your Process
- 28 Sponsor X
- 234 Events
- 7.3K Forum Archives
Check out the Formula Handbook template!