Forum Discussion
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 workday arguments that looks like this =IF(WEEKDAY(WORKDAY(D2,C2))>5,WORKDAY(WORKDAY(D2,C2),2), WORKDAY(D2,C2)). This formula seems to not count weekends though and gives me a date way in the future. Any thoughts?
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.)
7 Replies
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.)
- This is perfect. Problem Solved!
- Using the first suggestion I was able to tweak it a little to look like this, =IF(WEEKDAY(D2+C2)>5, D2+C2+CHOOSE(WEEKDAY(D2+C2),0,0,0,0,0,0,2), D2+C2). It now allows Fridays and pushes Saturdays to Monday, but I cannot get it to push Sunday no matter how I adjust the Choose statement. Any suggestions?
If you want to push Sunday to Monday, you can modify the CHOOSE statement as follows:
=IF(WEEKDAY(D2+C2)>5, D2+C2+CHOOSE(WEEKDAY(D2+C2),0,0,0,0,0,1,1), D2+C2)
In this modified version, Sunday (weekday number 1) is adjusted by adding 1 day, so it falls on Monday. The other days (Monday to Friday) are adjusted by adding 0 days.
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.
