locked
How do I subtract weekends in an Excel formula RRS feed

  • Question

  • Hi,

    I use the following formula to calculate the number of days between receipt of an application and the date of inspection.  However, we need to use business days to calculate the number of days, not calendar days.  Is there a way I can subtract weekend days from this formula in a worksheet:

    =IF(AND(J2="",A2=""),"",IF(J2=A2,1,IF(J2-A2<-100,"Not inspected Yet",IF(J2-A2=0,"",IF(J2-A2>10000,"No received Date",IF(J2<A2,1,J2-A2))))))

    Thank you,

    Ken


    Thank you, Ken

    Thursday, April 9, 2015 1:47 PM

Answers

  • Instead of J2-A2, use NETWORKDAYS(A2,J2)

    If you create a list of public holidays in a column and name the range Holidays, you can exclude public holidays from the count by using NETWORKDAYS(A2,J2,Holidays)


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by KMPeterson Thursday, April 9, 2015 3:20 PM
    Thursday, April 9, 2015 2:14 PM

All replies

  • Instead of J2-A2, use NETWORKDAYS(A2,J2)

    If you create a list of public holidays in a column and name the range Holidays, you can exclude public holidays from the count by using NETWORKDAYS(A2,J2,Holidays)


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by KMPeterson Thursday, April 9, 2015 3:20 PM
    Thursday, April 9, 2015 2:14 PM
  • Hi Hans,

    Thank you very much!!!  This worked very well.  I really appreciate your assistance.

    Ken


    Thank you, Ken

    Thursday, April 9, 2015 3:21 PM