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
Check out the Formula Handbook template!