Calculating payment dates to exclude weekends and holidays

Hi there

I am looking for assistence regarding a calculation. I want to be able to calculate a payment date scehdule based on a set monthly payment date but to exclude exclude weekends and specific holiday dates e.g.

Start date is 28 Jan 2012
The monthly payment date is 28th of each month
In theory the next payment date 28 Feb 2012
However, I need to calculate the next working day if the 28 Feb 2012 happens to fall on a weekend, or falls on a date within list of bespoke ad hoc holiday dates.

Any help would be greatly appreciated.

Regards

Mac

Have a look at the NETWORKDAYS function. That should do what you want.


Regards

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

Maybe in B5:

=B4+NETWORKDAYS($B$4,DATE(YEAR($B$4),MONTH($B$4)+1,DAY($B$4)),holidays)

or:

=$B4+NETWORKDAYS($B4,DATE(YEAR($B4),MONTH($B4)+1,DAY($B4)),holidays)

and copy down.

Not sure what dates you're expecting so you might have to play about a bit.


Regards

Hello Mac, did you find an answer to this yet?

You can use EDATE function in conjunction with WORKDAY. EDATE will add the correct number of month, WORKDAY will give you the next workday if that lands on a weekend or holiday - try this formula in B5 copied down

=WORKDAY(EDATE(B$4,ROWS(B$5:B5))-1,1,O$1:O$9)

Great solution however is there a way of reversing this so that if the 28th was to land either on a saturday, sunday or bank holiday it would default to the previous weekday ie friday or thursday if landed on good friday?

Many thanks

Just swap the -1 and the 1, i.e.

=WORKDAY(EDATE(B$4,ROWS(B$5:B5))+1,-1,O$1:O$9)

Thanks very much