Networkdays excluding holidays per country - calculate in hours

[Efthymios]

Dear all,

I am facing the following challenge: I have two timestamps and a location as columns and I am trying to calculate the time delta with the Networkdays formula in order to exclude the holidays and the non-working hours. If I have different holiday sets per country, which formula should I use to return the entire range for that specific country? Current formula:

=(NETWORKDAYS(Q7,R7,$C$6:$C$10)-1)*($D$3-$C$3)+IF(NETWORKDAYS(Q7,R7),MEDIAN(MOD(R7,1),$D$3,$C$3),$D$3)-MEDIAN(NETWORKDAYS(Q7,Q7)*MOD(Q7,1),$D$3,$C$3)

How should I get in the formula a multiple holiday set (F5:O20) instead of the fixed one in column C? The result in column U should be different.

Thank you in advance for the effort!

Efthymios

 

[Xiq]

Hi Efthymios

You can use the match and index functions to dynamically use the correct set of holiday dates.

=NETWORKDAYS(Start_date,End_date,INDEX(Holiday_dates,,MATCH(Location,Holiday_Locations,0)))

 

[Efthymios]

This is perfect! Thank you so much! Still haven't mastered the Index match formulas

 

[sgibeaut]

I tried your formula above but it isn't working for me. It will only count the last day as a holiday for each country. Perhaps I set up my holiday data wrong - I put it all in one long list with the country in col A and the date in Col b. Can you please help? I've spent a whole day on this
Sarah

 

[NARAYANK991]

Hi Sarah ,

It would help if you could upload your file ; however , the way you have described it , you cannot use the formula given above , since that formula expects each country to have its own column listing its holidays. The header will be the name of the country.

Since you have the countries all in one column , you need to change the given formula to :

=NETWORKDAYS(Start_date,End_date,OFFSET(Holiday_dates,MATCH(Location,Holiday_Locations,0)-1,,COUNTIF(Holiday_Locations,Location)))

where Holiday_dates is your range of holidays in column B , while Holiday_Locations is your range of countries in column A. You need to ensure that the country repeats in column A for all of its holidays e.g. if France has 6 holidays in a year , the text France should be present in 6 cells in column A. It should not happen that only the first holiday has France against it in column A , while the remaining 5 cells below it are all blank.

Narayan

 

[sgibeaut]

That works!! Thank you SO much. I wish I'd reached out for your help earlier instead of wasting 6 hours and becoming very frustrated. I could have put my holiday dates per country in separate columns too, but this is awesome. Thanks again.

 

[Parag Patel]

Excellent...worked for me too.