Direct Debit Payment Modeling

[CarlCarlos]

Hi All,

I am back again with another dates based query. I am looking to forecast my direct debit payments by week and need to model which week a payment will be taken from my bank account if it falls on a Saturday, Sunday or Bank Holiday...

Week1 Week2 Week3 ... etc
Sun 1st 8th
Mon 2nd 9th
Tue 3rd 10th
Wed 4th 11th
Thur 5th 12th
Fri 6th 13th
Sat 7th 14th

I want to build a model that says if my normal DD payment date is the 1st then the actual payment date will be the 2nd (in this example) or if the DD payment date is 7th then payment will be on the 9th.

Thank you all in advance

 

[ParamRay]

Assuming your date is in cell A1, try this worksheet formula:

=IF(WEEKDAY(A1)=1,1+A1,IF(WEEKDAY(A1)=7,2+A1,A1))