calculate networkday and exclude holiday

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!

Tags:

Answers

  • AravindGP
    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.



    Thanks,

    Aravind

    Reach out for any help on licenses, configuration, or training

  • freyafan
    freyafan ✭✭✭

    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
    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.

    Thanks,

    Aravind

    Reach out for any help on licenses, configuration, or training

  • freyafan
    freyafan ✭✭✭

    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
    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.


    Thanks,

    Aravind

    Reach out for any help on licenses, configuration, or training

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!