Remaining Network Days from End Date minus Today

[Jak]

Hi Guys

I have seen a bunch of formulas posted but still can't get my head around putting together a formula to do my calculation. On a spreadsheet I want to work out the number of remaining workingdays from an end date, minus bank holidays and todays date so that the remaining number of days is calculated and burn down to zero.

Cells A15:A25 are Bank Holiday Dates:

19/09/2022

26/12/2022

27/12/2022

02/01/2023

07/04/2023

10/04/2023

01/05/2023

29/05/2023

28/08/2023

25/12/2023

26/12/2023

I have an end date that I want to know the remaining amount of remaining network days, minus bank holidays from todays date. Example:

End_Date Cell: C12= 26/09/2022
Today() is 19/09/2022
Display number of workingdays remaining in Cell D12 Example

So if I look at Networkdays 19th Sept being today and 26th Sept being the End_Date, we have 6 working days, M,T,W,T,F,M If I include cell A15 as a non working day ie Bank Holiday on 19th September then 19th to 26th Sept is 5 working days from today, T,W,T,F,M.

On 19th Sept I want D12 to show 5 remaining days from today()
On 26th Sept I want D12 to show 1 remaining day from today()
On 27th Sept onwards I want D12 to show 0 remaining days from today()


Any example formula to put in cell D12 would be great.

 

[Fluff]

How about

=IF(TODAY()>C12,0,NETWORKDAYS.INTL(TODAY(),C12,,A15:A25))

 

[Jak]

How about

=IF(TODAY()>C12,0,NETWORKDAYS.INTL(TODAY(),C12,,A15:A25))

Hi Fluff, thank you so much.

 

[Fluff]

You're welcome & thanks for the feedback.