Forum Discussion

Victor1123's avatar
Victor1123
Copper Contributor
Dec 30, 2024

Removing Weekends in my if formula

I am trying to enter an if formula which takes into account only working days, so non-weekends and holidays. My formula is: =IF(O66-M66 <=3, "Yes", "No")

I want my date to show Yes/No if we met a deadline within 3 days, but need to omit the non-working days. 

Formulas and Functions
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Jan 03, 2025

    There is a catch for anyone like me who only uses array formulas.

    = LET(
      daysDL, NETWORKDAYS.INTL(+deadline, +actual) - 1,
      metDL?, IF(daysDL<=3, "Yes", "No"),
    HSTACK(daysDL, metDL?)
  )

    The + signs turn the multicell ranges into arrays.

    • Patrick2788's avatar
      Patrick2788
      Silver Contributor
      Jan 03, 2025

      A fascinating workaround. There's no need for a Lambda helper nor a need to index it to make it suitable for NETWORKDAYS.INTL.

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Jan 02, 2025

    Create a list of public holidays in a column, and name this range Holidays.

    (Select the range, click in the name/address box on the left hand side of the formula bar, type Holidays and press Enter)

    Change the formula to

    =IF(NETWORKDAYS.INTL(M66, O66, 1, Holidays)<=4, "Yes", "No")

    (I used 4 instead of 3 because NETWORKDAYS.INTL includes both the start date and end date in the count)

Resources