Home > Back-end >  Excel - How to consider Holidays and decrease the age count
Excel - How to consider Holidays and decrease the age count

Time:08-24

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

CodePudding user response:

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

CodePudding user response:

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

  • Related