Calculate the correct Ageing days

Hi -

I have a START Date and END Date and counts its ageing days.

Customer Name Start Date End Date Ageing
Individual April 29, 2016 May 2, 2016 3
Individual April 29, 2016 May 3, 2016 4
Individual April 29, 2016 May 5, 2016 6
Individual April 29, 2016 May 6, 2016 6

I also have one table where all the holidays are listed

Date Holiday Name Customer Name
May 2, 2016 Holiday Individual
May 6, 2016 Holiday Individual


What I need is to compute for the ageing days. How? This is the requirement for the condition.

If the Start Date falls on the last business day of the month and on a Friday, the ageing days should not be considered. As to Saturday and Sunday should not be counted only for that week. Then if there is a holiday/s between the start date and end date, will not also be considered in the Ageing days.

SAMPLE NEW AEGING DAYS

Customer Name Start Date End Date NEW Ageing
Individual April 29, 2016 May 2, 2016 0
Individual April 29, 2016 May 3, 2016 1
Individual April 29, 2016 May 5, 2016 3
Individual April 29, 2016 May 6, 2016 3


What would be the best macro code or best formula for this. Thank you.

Try this :

Please Login or Register  to view this content.

Here $M$2:$M$3 contains the holidays.