Calculate Payment date based on same day of month - allow for weekends and holidays

[roomale]

I'm working on a personal cashflow statement and am stuck on how to write a formula which will allow me to calculate the payment date for each future payment based on the following criteria

1. I know the first payment date - C6 (14-Sep-12 - a Friday)
2. The next payment date will be calculated in C8 and will be the same day (14) in the next month, unless :
The 14th is a weekend - in which case I want to move the payment date to the Monday
14th Oct is a Sunday therefore I want the payment to be calculated to be 15th Oct (Monday)

If 15th Oct was a Bank Holiday then I would want the date advanced to 16th Oct

When I move down a month to November - I want the payment date to be on the preferred date 14th Nov unless either of the above apply

Any help to work out which of the Excel 2007 date calculation methods is best to use for this would be appreciated

Thanks

Roo

 

[barry houdini]

Hello Roo,

If the dates should be in C8 down then put this formula in C8 and copy down

=WORKDAY(EDATE(C$6,ROWS(C$8:C8))-1,1,H$2:H$10)

Assumes a list of holiday dates in H2:H10, change that range as required

 

[roomale]

Hello Roo,

If the dates should be in C8 down then put this formula in C8 and copy down

=WORKDAY(EDATE(C$6,ROWS(C$8:C8))-1,1,H$2:H$10)

Assumes a list of holiday dates in H2:H10, change that range as required

Thanks Barry .. that looks to have moved me forward .. much appreciated

Roo