Forum Discussion
Solved
Ways to count from a select date including weekends but have it output only on the closest weekday
I'm trying to take a select date and add a certain number of days to it and output the new date but the output date must be a weekday. I am trying to use an if statement with nested weekday and workd...
R_Caron418 See the attached workbook, especially the notes on the _Info worksheet.
(Note that the column headings for columns E through I are derived from the formulas directly below them.)
The WORKDAY function is designed to exclude weekends by default. If you want to include weekends but adjust to the closest weekday if the result falls on a weekend, you can modify your formula as follows:
=IF(WEEKDAY(D2+C2)>5, D2+C2+CHOOSE(WEEKDAY(D2+C2),2,2,2,2,2,4,4), D2+C2)
Explanation:
- D2+C2 calculates the date after adding the specified number of days.
- WEEKDAY(D2+C2)>5 checks if the resulting day is Saturday or Sunday (weekday numbers greater than 5).
- If it's a weekend, CHOOSE(WEEKDAY(D2+C2),2,2,2,2,2,4,4) adds 2 days for Saturday and 4 days for Sunday to make it fall on the following Monday.
- Otherwise, if it's a weekday, D2+C2 is used as is.
This formula should give you the next weekday if the result falls on a weekend. Adjust the starting date (D2) and the number of days to add (C2) according to your needs.
The text and steps were edited with the help of AI.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and Like it!
This will help all forum participants.
NikolinoDE this helped to get a lot closer to the date however when I add my first date which is 11/17/2023 with the number of days which is 63.33 it outputs 1/23/24 when it if you simply add them, it would be 1/19/24 which is a Friday. I'm a little confused why its skipping Friday.