Working days excluding holidays

[wildturkey]

Afternoon All

Just looking to drag down a date but with Saturdays, Sundays and holidays excluded...

I can get this to work to give me my list excluding Saturdays and SUndays, but the holidays is beyond me, any help appreciated..

Thanks

 

[wildturkey]

Sorry, trying to delete this as my question is not clear

 

[Sufiyan97]

You can not delete the thread instead you can modify your question by replying to your own thread

 

[wildturkey]

Cannot find my addins or XL2BB but will try and elaborate..

In Cell A1 I have a date, in A2 I'd like a formula that can be dragged down that adds one day to the day above but excludes weekends or holidays in a list.

I have the following which works until I get a month where there are two consequtive holidays...

=IF(ISERROR(MATCH(WORKDAY(EOMONTH(A1,-1),1),Sheet2!$B$1:$B$12,0)),WORKDAY(EOMONTH(A1,-1), 1),WORKDAY(EOMONTH(A1,-1), 1)+1)

Fingers crossed

 

[Fluff]

Is B1:B12 a list of the Holidays?

 

[wildturkey]

yes please

 

[Fluff]

In that case how about

+Fluff 1.xlsm
	A
1	17/12/2021
2	01/12/2021
3	02/12/2021
4	03/12/2021
5	06/12/2021
6	07/12/2021
7	08/12/2021
8	09/12/2021
9	10/12/2021
10	13/12/2021
11	14/12/2021
12	15/12/2021
13	16/12/2021
14	17/12/2021
15	20/12/2021
16	21/12/2021
17	22/12/2021
18	23/12/2021
19	24/12/2021
20	29/12/2021
21	30/12/2021
Data

Cell Formulas
Range	Formula
A2	A2	=WORKDAY(EOMONTH(A1,-1),1,Sheet2!$B$2:$B$12)
A3:A21	A3	=WORKDAY(A2,1,Sheet2!$B$2:$B$12)

 

[wildturkey]

So much simpler than mine and works way much better - Thank you!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Fluff]

If you only wanted the working days for the month you could also use

+Fluff 1.xlsm
	A
1	17/12/2021
2	01/12/2021
3	02/12/2021
4	03/12/2021
5	06/12/2021
6	06/12/2021
7	06/12/2021
8	07/12/2021
9	08/12/2021
10	09/12/2021
11	10/12/2021
12	13/12/2021
13	13/12/2021
14	13/12/2021
15	14/12/2021
16	15/12/2021
17	16/12/2021
18	17/12/2021
19	20/12/2021
20	20/12/2021
21	20/12/2021
22	21/12/2021
23	22/12/2021
24
Data

Cell Formulas
Range	Formula
A2:A23	A2	=LET(Hols,Sheet2!$B$2:$B$12,Start,EOMONTH(A1,-1),WORKDAY(SEQUENCE(NETWORKDAYS(Start,EOMONTH(A1,0),Hols),,Start),1,Hols))
Dynamic array formulas.