CODEWITHSUNDEEP
excelexcel-formula
Kannan Prince
Kannan Prince

Reputation: 23

Excel - How to consider Holidays and decrease the age count

I have Opened Date and Resolved date but in between opened and resolved date there is a holiday (Consider July 4th). I need to reduce "1" count from the age if the holiday falls in between Opened and Resolved Date. Below picture is the sample data...

Also if there are multiple holidays in between the opened and resolved date, the age should be decreased by "2"

enter image description here

Upvotes: 0

Views: 110

Answers (2)

Harun24hr
Harun24hr

Reputation: 36880

As Scott mention in comment you can use NETWORKDAYS.INTL() function to indicate weekends in your country. If it is Saturday-Sunday then can use NETWORKDAYS() function. Try below-

=NETWORKDAYS(A2,B2,$G$2)
=NETWORKDAYS.INTL(A2,B2,1,$G$2)

enter image description here

Upvotes: 1

j2associates
j2associates

Reputation: 1155

Assuming your Holidays are in G2..K2..

  1. Put this formula in F2, it will count the number of Holidays > A2 and < B2

    =COUNTIFS(G2:K2,">"&A2,G2:K2,"<"&B2)

  2. Put this formula in C2 to pick up the value in F2

    =B2-(A2+F2)

Excel COUNTIFS Function

Enjoy...

Upvotes: 1

Related Questions