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.)
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.