Include certain weekend in workday formula

[szita2000]

Hi Guys.

Long time lurker here.
The forum is a godsend. I am visiting it on a daily basis, and I have to thank to all you good people out there all the excel knowledge I gathered.
Here is something that I can't solve.

Attached worksheet: test solution for workdays.xlsx

This is a worksheet set up to create a "calendar" horizontally that only include workdays of a given month. This would be simple enough.
However, when we are getting close to a month end or a Quarter end, we might want to add a few weekends to deal with the volume we have to cater for.
(I know it would be a lot simpler to include all weekends and zero out whatever we don't want to cater for, but I enherited this "troublesome" format and my manager don't want to know anything else.)
Well. that's life I suppose
To create Saturdays and Sundays would be as simple as -1 from a Monday's value to add in a Sunday, or -2 to add in a Saturday in Row1.

I have worked it out so far, where by I am finding the workdays after the weekends from the right to the left. As 1,2. 3,4. Mondays and Tuesdays respectively.
I am trying to add checkboxes and add in an if statement to row 1 saying if we check checkbox 1 then find 1,2 and minus out the neccesary to create Sat and Sun.
Unfortunately when I try to do this it tells me that this would result in a circular reference.
Wonder if there is a workaround to this, because I am deriving the days that follow the weekends, from the date I want to modify.

PS: every yellow cell in the sheet is an input and set to be modified.

Any help is most appreciated.

Thanks

Tom.

[natefarm]

I'm not sure if you want it to "add a few weekends", as you said above, or if you want to "add last weekend", as it says with the checkbox in your workbook. If you copy the formula below to cell U1, then drag it right through AD1, it will work for the last two weekends.

Please Login or Register  to view this content.

To make it more flexible than that (like with a variable indicating how many weekends) would be more of a challenge, but you could easily adjust it from one month to the next by just dragging the previous formula (in T1) to the right as far as needed, or dragging the new formula to the left as far as needed.

[szita2000]

Thanks natefarm.


It is challenging indeed. I am pretty sure that the way I want to do it would result in a circular reference. I will have to spend some more time on this to make it bulletproof.
Right now I am exploring the vba possibilities, when the checkbox is checked the vba code will find the first instance and would -2 out of the date value.
We will see how it goes.

Thanks for your help!

[natefarm]

Does the above formula not work, as far as it goes?

[szita2000]

Yes it does.
Maybe I will try to section it out for the weeks and add in a few more checkboxes.
I think I might just have to get rid of the exclusion of days so it will not mess up everything.

Thanks again!