calculate networkday and exclude holiday

freyafan

Hi all,

I am trying to use formula to calculate the manufacturing dates between 2 dates for a factory that located a country and exclude holidays only when this country has these holidays, holiday list is in another sheet. How can I use networkday and IF formulas to achieve it?

Thank you!

AravindGP

Hi @freyafan

I assume you have holidays added in the second sheet as rows. You can click on the "Reference another sheet" link when you get to holidays and select the column containing the list of holiday dates.

freyafan

https://community.smartsheet.com/discussion/comment/401512#Comment_401512

Thanks Aravind, the problem is I have all countries' holidays in the second list, when I am just simply refer to the holiday list, it will exclude all holidays, but I only want to exclude some holidays when it meet one criteria. For example, I want to calculate a Chinese Factory's manufacturing duration, I only want to remove Chinese Holiday. The formulas I am using right now is:

(NETWORKDAY([STARTED DATE]@row, [FINISHED DATE]@row, {HOLIDAY INTAKE FORM Range1})

AravindGP

Hi @freyafan

It is becoming interesting now. Given the formula you're using, I believe you have a column for each country. Depending on the number of countries, you can do a nested If formula.

=IF(Countrycolumn@row = "China", NETWORKDAY([STARTED DATE]@row, [FINISHED DATE]@row, {HOLIDAY INTAKE FORM Range1}), IF(Countrycolumn@row = "Hong Kong", NETWORKDAY([STARTED DATE]@row, [FINISHED DATE]@row, {HOLIDAY INTAKE FORM Range2})... etc.

freyafan

https://community.smartsheet.com/discussion/comment/401526#Comment_401526

Almost! The only problem right now is I have all countries' holidays in one column in the second list, so I don't think I can refer to another sheet with 2 or more ranges, let me attached a photo for you:

I tried to use IF to filter the holiday as below, but it doesn't work:

=IFERROR(NETWORKDAY([STARTED DATE]@row, [FINISHED DATE]@row, IF({HOLIDAY INTAKE FORM Range 1} = "China", {HOLIDAY INTAKE FORM Range 2}), " "))

AravindGP

Hi @freyafan

I would suggest to have just one date column to contain all holiday dates instead of a start and end date. You can still have the name of the holiday and countries. When you do that, your list will look something like the below. You can then use this formula.

=IF(Countrycolumn@row = "China", NETWORKDAY([STARTED DATE]@row, [FINISHED DATE]@row, COLLECT({Date column}, {Countries column}, "China")))

You could continue the formula for other countries.

Run Wild

Thanks, great concept! This helped me. :) I think this needs to use "NETWORKDAYS" function to work, not "NETWORKDAY".

Corrected:

=IF(Countrycolumn@row = "China", NETWORKDAYS([STARTED DATE]@row, [FINISHED DATE]@row, COLLECT({Date column}, {Countries column}, "China")))