How to calculate total days using networdays.intl with condition state list holiday
- Thread starter rafiz87
- Start date
GraH - Guido
Well-Known Member
Maybe this works
=NETWORKDAYS.INTL(C5;D5;XLOOKUP(B5;Holiday!$G$2:$G$8;Holiday!$F$2:$F$8);FILTER(Holiday!$B$2:$B$7;Holiday!$A$2:$A$7='Total Days'!A5;0))
=NETWORKDAYS.INTL(C5;D5;XLOOKUP(B5;Holiday!$G$2:$G$8;Holiday!$F$2:$F$8);FILTER(Holiday!$B$2:$B$7;Holiday!$A$2:$A$7='Total Days'!A5;0))
Thank you. instead using xlookup and filter, do you have other method? currently i use excel 2016. i try use xlookup and filter cannot work
GraH - Guido
Well-Known Member
If you keep the set-up of the holidays grouped per state, this seems to work:
=NETWORKDAYS.INTL(C2;D2;VLOOKUP(B2;Holiday!$G$2:$H$8;2;FALSE);IFNA(OFFSET(Holiday!A1;MATCH(A2;Holiday!$A$2:$A$7;0);1;COUNTIF(Holiday!$A$2:$A$7;A2));0))
=NETWORKDAYS.INTL(C2;D2;VLOOKUP(B2;Holiday!$G$2:$H$8;2;FALSE);IFNA(OFFSET(Holiday!A1;MATCH(A2;Holiday!$A$2:$A$7;0);1;COUNTIF(Holiday!$A$2:$A$7;A2));0))
if use this formula let say from start date to end date after holiday or before holiday, Is it this formula will calculate also for countif holiday too? supposedly did not countif in list holiday right? please correct me if i wrong
GraH - Guido
Well-Known Member
I see I did a mistake in the offset formula. The starting position needs to be an absolute reference OFFSET(Holiday!$A$1
OFFSET can return a array. In the example of Melaka it returns 2 dates 5/01/2024 and 6/01/2024. Because we have 2x Melaka in the list of hoilidays. Hence the countif used. These days are used in the argument holidays of NETWORKDAYS.INTL function. That function corrects the number of working days taking into account the list of holidays. So if the start and end date are both before or after the holidays will have no effect on the calculation. In that scenario the holidays are simply omitted.
I can advise you to use the formula auditor to learn how the formula works.
After pressing evaluate a couple of times, you will see the following. It also shows you how the formula is calculated.
OFFSET can return a array. In the example of Melaka it returns 2 dates 5/01/2024 and 6/01/2024. Because we have 2x Melaka in the list of hoilidays. Hence the countif used. These days are used in the argument holidays of NETWORKDAYS.INTL function. That function corrects the number of working days taking into account the list of holidays. So if the start and end date are both before or after the holidays will have no effect on the calculation. In that scenario the holidays are simply omitted.
I can advise you to use the formula auditor to learn how the formula works.
After pressing evaluate a couple of times, you will see the following. It also shows you how the formula is calculated.
Last edited:
oh i see. may i know, which part need to use for OFFSET(Holiday!$A$1 into this formula ?
=NETWORKDAYS.INTL(C2;D2;VLOOKUP(B2;Holiday!$G$2:$H$8;2;FALSE);IFNA(OFFSET(Holiday!A1;MATCH(A2;Holiday!$A$2:$A$7;0);1;COUNTIF(Holiday!$A$2:$A$7;A2));0))
GraH - Guido
Well-Known Member
=NETWORKDAYS.INTL(C2;D2;VLOOKUP(B2;Holiday!$G$2:$H$8;2;FALSE);IFNA(OFFSET(Holiday!$A$1;MATCH(A2;Holiday!$A$2:$A$7;0);1;COUNTIF(Holiday!$A$2:$A$7;A2));0))
Thank you for your assistance. let me try
Just to ask, what tool you use ya? excel file or WPS? i check in WPS this tab formula have it.. but in my excel evaluate formula did not active
GraH - Guido
Well-Known Member
Did you select the cell, with the formula? Evaluate is on the ribbon since many versions prior to Excel 2016.
See #4, you can open that file. Only correct the formula as in #8
For the error, might it be you have to use a comma "," as argument separator in the formula where I have semi-colon ";"?
See #4, you can open that file. Only correct the formula as in #8
Its works. thank you for your assistance. can this formula to use in power query, dax (power bi)?
GraH - Guido
Well-Known Member
I do not know of any networkdays-alike function in Power Query. Having said this, with some creativity it can be created with list operators for sure. Google it and you may find some custom made functions by MVP or Power Query experts.
DAX knows the function and as far as arguments go is quite similar to Excel's function. However DAX looks simple at first glance, I can tell you it is not that easy to truly master. You can't simply replicate any excel formula in DAX.
DAX knows the function and as far as arguments go is quite similar to Excel's function. However DAX looks simple at first glance, I can tell you it is not that easy to truly master. You can't simply replicate any excel formula in DAX.
Thank you for your advice and guidance.