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"
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)
CodePudding user response:
Assuming your Holidays are in G2..K2..
Put this formula in F2, it will count the number of Holidays > A2 and < B2
=COUNTIFS(G2:K2,">"&A2,G2:K2,"<"&B2)
Put this formula in C2 to pick up the value in F2
=B2-(A2 F2)
Enjoy...