a networkday formula that excludes holidays and multiple leave date entries by employee

[gibsarum]

I am desperately seeking a networkday formula that calculates for each employee:

the available working days between 2 dates (the below is on a tab named 'Project' with Employee in column A),

ROW A B C
1 Employee Start End
2 AS123 15/01/2024 16/02/2024
3 AP345 10/03/2024 14/03/2024
4 AP346 29/12/2023 29/03/2024
5 AP234 5/12/2023 2/02/2024

Which excludes public holidays (the below is on a tab named 'NSW Public Hol' with dates in column D),

D
Labour Day 2/10/2023
Christmas Day 25/12/2023
Boxing Day 26/12/2023
Picnic Day 27/12/2023
New Year's Day 1/01/2024
Australia Day 26/01/2024
Good Friday 29/03/2024
Easter Saturday 30/03/2024
Easter Sunday 31/03/2024
Easter Monday 1/04/2024
Anzac Day 25/04/2024
King's Birthday 10/06/2024
bank holiday 5/08/2024
Labour Day 7/10/2024
Christmas Day 25/12/2024
Boxing Day 26/12/2024

and also excludes any leave holidays for the employee (located in a third tab). the leave start and end periods taken by each employee are below:

ROW A B C
1 Employee Start End
2 AS123 11/02/2024 19/02/2024
3 AP345 12/03/2024 19/03/2024
4 AS123 7/02/2024 10/02/2024
5 AP346 10/02/2024 19/02/2024
6 AP234 10/02/2024 19/02/2024
7 AP345 10/02/2024 19/02/2024

As you may see, employee AS123 has a project starting on the 15/01/2024 and ending on the 16/02/2024.

Employee AS123 has leave start on the 11/02/2024 and end 19/02/2024. this means, 4 days of the holiday are during the project period.
Employee AS123 also has another leave start on the 7/02/2024 and end 10/02/2024. this means, another 4 days of the holiday are during the project period. In total, 8 days of leave during the project period across 2 different entries.

Australia day is on the 26/01/24 which also needs to be excluded form the working days available.

I have tired a few formulas including this on albeit, didn't capture all the leave dates that fell within the project period as per the attached spreadsheet:

=NETWORKDAYS(B2, C2)-SUMPRODUCT(--(Leave!$A$2:$A$100=A2), --(Leave!$B$2:$B$100<=C2), --(Leave!$C$2:$C$100>=B2)) - COUNTIFS('NSW Public Hol'!$D$2:$D$100,">="&B2, 'NSW Public Hol'!$D$2:$D$100,"<="&C2)

This is my first post and I thank you all in advice for you help. I've been on this now for 3 days and thought it best to reach out to the experts

[AliGW]

Administrative Note:

Welcome to the forum.

Is your forum profile showing the Excel PRODUCT that you need this to work for?

Members will tailor the solutions they offer to the Office PRODUCT (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your product is for Mac, please also state this.

The three most recent Excel products are Excel 2019, Excel 2021 and MS365 - if you are using MS365, please give this name along with the version number in your profile (e.g. MS365 Version 2306). This is in the About Excel section further down the Account page.

Thanks.

[AliGW]

Just a point to note - you can exclude holiday dates thus:

=NETWORKDAYS(B2, C2,'NSW Public Hol'!$D$1:$D$16)-SUMPRODUCT(--(Leave!$A$2:$A$100=A2), --(Leave!$B$2:$B$100<=C2), --(Leave!$C$2:$C$100>=B2))

[gibsarum]

yaama AliGW - thank you for the reply!

I am using Microsoft Excel for Microsoft 365 MSO (Version 2310 Build 16.0.16924.20054) 64-bit

warm regards and many thanks

[AliGW]

Your profile needs 365 adding, please.

You have failed to provide your expected results, so everything is guesswork.

Does this get you any closer?

=NETWORKDAYS(B2, C2,'NSW Public Hol'!$D$1:$D$16)-SUM(NETWORKDAYS(FILTER(Leave!$B$2:$B$7,Leave!$A$2:$A$7=A2), FILTER(Leave!$C$2:$C$7,Leave!$A$2:$A$7=A2),'NSW Public Hol'!$D$1:$D$16))

[gibsarum]

yaama AliGW and thank you - this formula although much cleaner than the one I posted, does not capture the dates in the Leave tab for exclusion from working days. The other addition to the fomula is that the employee may have several projects on at the same time with leave needing to be noted across each entry... because my original query wasn't complex enough lol.

I look forward to your reply

[AliGW]

Nothing more from me until you add expected results to the sample workbook.

[gibsarum]

thanks again AliGW

I've updated the version of excel as requested #gettingthere

What I am after is below. I want to know the available working days of an employee, during a project, which also considers public holidays and their leave.

Does that make more sense?

Your suggested formula worked like a treat for the first row #huzzah
However as I dragged down, it wasn't as precise.

Any further suggestions are most appreciated

File attached.


Employee Start End Networkdays result wanted
AS123 15/01/2024 16/02/2024 15 15
AP345 10/03/2024 14/03/2024 -8 1
AP346 29/12/2023 29/03/2024 57 55
AP234 5/12/2023 2/02/2024 33 23

[AliGW]

I didn't say that I didn't understand what you want. I asked you to add expected results to the workbook, but never mind - I can copy them from here.

[gibsarum]

yaama AliGW - file edited and attached to the last reply

[gibsarum]

my apologies AliGW

Your formula worked for all apart from the second.

employee Start end Networkdays result wanted
AS123 15/01/2024 16/02/2024 15 15
AP345 10/03/2024 14/03/2024 -8 1
AP346 29/12/2023 29/03/2024 57 57
AP234 5/12/2023 2/02/2024 33 33

wooHOO!

The -8 should be a 1.

Apologies for the confusion and my poor addition lol

[gibsarum]

yaama AliGW - file V2 uploaded with the amendments - only 1 now incorrect, the cell with -8.

[AliGW]

My formula won't work unless the leave dates are fully within the range required. I am working on it.

[gibsarum]

Thank you so much AliGW!
I'm so grateful and, the best way to rap up 2023!

[gibsarum]

yamma AliGW and a happy NYE to you!

It's a tricky problem - not an easy fix that's for sure!
How did you get on?

Thanks so much

[HansDouwe]

Here is a solution:

Please try in E2 and copy down:

Please Login or Register  to view this content.

I think 2 expected results are wrong. See attachment.

[AliGW]

How did you get on?

Erm ... It was New Year's Eve, so I wasn't going to spend all of it on your problem, sorry!!!

I would have resumed today, but it looks as if Hans has solved it for you.

[gibsarum]

yamma HansDouwe - this is MAGNIFICENT!

You are right on all accounts!

WOW, Thank you for making the impossible from my position, possible. I'm so grateful!

I do have two more problems to solve:

1) Amend the above formula to calculate working days left. I did a calculation that changed the start date to today() however, for end dates that passed, it returned an error. I would like the result to return the days passed as negative values.

2) as per the upload, in the Project tab, column 1, I would like to count the number of records, during the project period, where leave was due to sickness. As per the Leave tab here are 2 types of Sick leave as displayed in column C; 'Sick Leave - MC' and 'Sick without Pay - MC'. I would like to count both types of leave.


Thank you so much - what a legend!

[gibsarum]

yaama AliGW and indeed fair one!


Thank you so much for your workings on this query. I greatly appreciate it


A stellar evening to you and the happiest beginnings to 2024!

[HansDouwe]

Thanks for the feedback and nice words. Glad to have helped. .

I would like the result to return the days passed as negative values.

Could you show an example of that? Could you also show an example where a part of the days is passed so a part should be counted as positive and a part as negative.

And I don't understand the expected reults of the number of Sick leave dates.
AS123: Expected results: 2, but I'm counting 5: Februari 12, 13, 14, 15 and 16.
AP234: Expected results: 1, but I'm counting zero: AP234 has only Sick Leave days after the end date.

[gibsarum]

yaama HansDouwe - I should write LEKKER!

I have added some examples in the Project tab for:
column D - actual end (this is the example of days under and days over the end date
column H - result wanted for example of actual end date
column J - number of sick leave days for end date
column K - number of sick days for actual end date

The aim is to capture working days of the actual end date of the project (column H - result wanted for example of actual end date) when a date is entered in column H.

Your understanding is partly correct for sick leave.
You were very right, my dates were 'very wrong' lol

I would like to count the number of records, during the project period, where leave was due to sickness. I want to include any of the descriptions in the leave tab, column E , that contain the word 'sick'.

For AS123: Expected results in Project tab, J2 should equal 8: Februari 7, 8, 9, 12, 13, 14, 15 and 16.
For AP234: the result should be 0 as you've rightly stated

I very much look forward, with much thanks!

[HansDouwe]

I don't understand the new examples with the actual end date:

Employee AS123 Expected result is 16 so you are counting the Networkdays from Start (Jan 15 2024) until Actuel End (Feb 14 2024).

Employee AP345 Expected result is 1,but if I count the Networkdays from Start (Mar 10 2024) until Actuel End (Mar 13 2024) I count 3 Networkdays (Mar 11, 12 and 13).

Employee AP346 Expected result is 28,but if I count the Networkdays from Start (Dec 29 2023) until Actuel End (Feb 19 2024) I count 29 Networkdays.

Employee AP234 Expected result is -6. but if I count the Netwotkdays from Start (Dec 5 2023) until Actuel End (Feb 20 2024) I count even more as 39 days.
In line with the above examples, I would find it logical that when the actual end date is greater than the end date, the network days are calculated from Start Date to End Date and should therefore be 39.

[gibsarum]

My apologies for the confusion HansDouwe


Kindly allow me try and explain:

Employee AP345 Expected result is 1,but if I count the Networkdays from Start (Mar 10 2024) until Actuel End (Mar 13 2024) I count 3 Networkdays (Mar 11, 12 and 13). The 12/03/2024 to the 19/03/2024 are leave hence the Networkdays are 1.

Employee AP346 Expected result is 28,but if I count the Networkdays from Start (Dec 29 2023) until Actuel End (Feb 19 2024) I count 29 Networkdays. You are correct

Employee AP234 Expected result is -6. but if I count the Netwotkdays from Start (Dec 5 2023) until Actuel End (Feb 20 2024) I count even more as 39 days. This should read -6 to reflect that the actual finish date is 6 days over the estimated end date.

The purpose of the actual end date is to highlight the difference between the estimated end and actual end (if there are any). The negative operates as a way of easily identifying which project have gone over their forecasted dates and by how many days (i.e. -6 means the project has gone over the forecast by 6 days, including weekends, public holidays and leave taken).

Kindly advise if this makes sense or if I need to offer a clearer explanation.


I appreciate that my explanation may not be clear so you are right to challenge!

[HansDouwe]

The 12/03/2024 to the 19/03/2024 are leave hence the Networkdays are 1.

O yes you are correct.

Now everything is clear to me and I will start working with the formulas.

[HansDouwe]

Formula Networkdays Actual End date:

Please try in G2 and copy down:

Please Login or Register  to view this content.

[HansDouwe]

Formula number of sick leave days end date:

Please try in J2 and copy down:

Please Login or Register  to view this content.

Formula number of sick leave days actual end date:

Please try in K2 and copy down:

Please Login or Register  to view this content.

[DJunqueira]

I like reading formulas with table names, maybe it helps..

Please Login or Register  to view this content.

[gibsarum]

LEKKER BEZIG! MOOI ZO! TOPPIE!

I cannot thank you enough! This is brilliant and full of much learning for me!

My new year feels great.

With much gratitude to you and wishing you and family a week as exciting as mine has begun!

Thank you

[josephteh]

With a Helper Column in Leave sheet,
D2=IF(IF(OR(B2>INDEX(Project!$C$2:$C$5,MATCH($A2,Project!$A$2:$A$5,0)),C2<INDEX(Project!$B$2:$B$5,MATCH($A2,Project!$A$2:$A$5,0))),"-",MAX(INDEX(Project!$B$2:$B$5,MATCH($A2,Project!$A$2:$A$5,0)),B2))="-",0,NETWORKDAYS(IF(OR(B2>INDEX(Project!$C$2:$C$5,MATCH($A2,Project!$A$2:$A$5,0)),C2<INDEX(Project!$B$2:$B$5,MATCH($A2,Project!$A$2:$A$5,0))),"-",MAX(INDEX(Project!$B$2:$B$5,MATCH($A2,Project!$A$2:$A$5,0)),B2)),IF(IF(OR(B2>INDEX(Project!$C$2:$C$5,MATCH($A2,Project!$A$2:$A$5,0)),C2<INDEX(Project!$B$2:$B$5,MATCH($A2,Project!$A$2:$A$5,0))),"-",MAX(INDEX(Project!$B$2:$B$5,MATCH($A2,Project!$A$2:$A$5,0)),B2))="-","-",MIN(INDEX(Project!$C$2:$C$5,MATCH($A2,Project!$A$2:$A$5,0)),C2)),'NSW Public Hol'!$D$1:$D$16))

Net Working Days =NETWORKDAYS(B2,C2,'NSW Public Hol'!$D$1:$D$16)-SUMIFS(Leave!$M$2:$M$7,Leave!$A$2:$A$7,A2)

[HansDouwe]

Graag gedaan.

Thanks for the nice feedback.

If there any questions left don't hesistate to ask these questions here.

Glad to have helped.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

[gibsarum]

Hoi Hans!

How are you?

I have a new question re: this thread for your marvellous mind to solve!

The formula that you provided works phenomenally to determine working days between 2 dates which considers holidays, leave and public holidays.

I am now challenged to work our the following scenario.

If I have an end date, and the number of 'available working days' a task is estimated to be performed (lets say for 32, or 25, or 20), what is the start date?

I look forward to your thoughts

[HansDouwe]

Please upload a sample workbook with manually added expected results.

[gibsarum]

Hoi Hans - of course and my pleasure

Kindly note that the sample answers are located in tab Project Cells D3:F4

I have also added B41:C43 however was unable to add them to the same array as the other dates in column B.

[HansDouwe]

Thanks for the feedback and rep. Glad to have helped. .

About your last question:

Please try in D5 and copy to the right and down:

Please Login or Register  to view this content.

Note: The formula returns in D5 February 20 instead of February 19, because empl. AS123 is still on Leave February 19.

[gibsarum]

LEKKER Hans!

My pleasure and many thanks to you my friend

A new query is if I now have the end dates (represented in D4:F5), how may I reengineer the formula to count available working days backwards, as opposed to forwards?

The sample results are now represented in G4:I5 with G3:I3 highlighting the available working days to be counted from the end dates.

Dank je

[HansDouwe]

I discovered an error in the formula of post #34.
There is one $ sign too many in the formula. The correct formula is.

Please try in D3 and copy to the right and down:

Please Login or Register  to view this content.

Note: The formula returns in D5 February 20 instead of February 19, because empl. AS123 is still on Leave February 19.

[HansDouwe]

About your queston in Post #33, I don't think, your expected results are correct.

Please try and copy to the right and down:

Please Login or Register  to view this content.

[gibsarum]

yet again Hans, you are correct!

I had transposed your formula into my workbook, amended the parameters and it now returns the #VALUE! error.
=LET(l,'Leave Sheet'!$AG$2:$AI$350,h,'NSW holiday 2024'!$D$1:$D$99,
WORKDAY($AE6+1,-AQ$3,VSTACK(h,DROP(REDUCE("",INDEX(l,,1),LAMBDA(i,x,IF(x<>$I6,i,VSTACK(i,SEQUENCE(1+OFFSET(x,,2)-OFFSET(x,,1),,OFFSET(x,,1)))))),1))))


$AE6 is the end date
$AQ3 is the number of working days backwards from the end date
$I3 is the employee (number)

I have no idea what I am doing wrong?
Data is in all the columns. I cannot see where I am going wrong?

[AliGW]

You'd need to provide a sample workbook where the formula is failing.

Would you PLEASE ensure going forward that your expected resuls are ACCURATE? Members rely on them when testing solutions. Thanks.

[gibsarum]

Thank you for your feedback Ali and understood

[gibsarum]

Het spijt me Hans

Thank you for your persistence with my confusion and will ensure that my wanted results are correct prior to engagement.

Kindly find attached my spreadsheet where the formula isn't working for every result

The error may be seen in cells I13:L17 in Fcast tab.

I believe the error is because employee number 11045848, is not in the Leave Sheet.

May the formula be amended to calculate available working days without personal leave if the employee number is not in the Leave Sheet?

Dank je

Thanks again in advance

[HansDouwe]

Please replace "" by 0 and remove DROP( .... ,1) from the formula:

Please Login or Register  to view this content.