Subtract days excluding weekend, holidays

[RahulM]

I want to subtract 10 days from a date and get result date excluding weekend and holidays.
Also, if issue date is less than result date then get remark as In TAT otherwise Out of TAT.

I am able to complete first step of subtracting 10 days from date but do not know how to exclude weekends or holidays. And caluculate difference in dates to update any comment.

Would appreciate your help in this regard.

Details:

1. Column S has Client Appointment Date
2. Column V has Issue Date
3. Column AA – Should get Result Date
4. Column Z – remarks

On update of Column S, Column AA should immediately give date subtracting 10 days from Client Appointment Date
But, date should not be weekend or holiday. If it is then result should be one day prior to Saturday and holiday and two days prior to Sunday.

On update of Issue Date, Column Z (Remarks) should give date compare result as "In TAT" or "Out of TAT", basis, Issue date is less than TAT date or not.

I have attached sample file for your reference.

 

[tomas]

see formula in AB3, as argument weekend = default or 1 is sunday and saturday, for the argument holiday you need to have a range of cells containing holidays a pass it into function as argument

 

[RahulM]

Dear tomas,

Thank you for your help.
However, I am looking for solution using VBA.

Currently I am using excel formulae to exclude weekend, holidays and update comment if Issue date is less than or greater to Result date.

Now trying to find a solution through VBA.

 

[RahulM]

Hi,

I worked on my VBA code further and now I am able to exclude weekend and holidays. So it has completed my first requirement.


Now remaining one is to compare dates and update remarks accordingly.


Here too I have written a VBA code which does update remark basis date comparison. But I am unable trigger this using Worksheet_Change method.

I don't know method of writing multiple Worksheet_Change condition and loop so would appreciate help in this regard.


I have attached sample revised file with updated VBA code for reference.