how to calculate dates using vba to find when a check is payable

[ids_73]

hi there
i am looking to calculate when a check is payable using vba.
the parameters are:
1)the check has to be cashed at the same bank otherwise it will be payable in 3 day after the payable date.
2)the payable date must be a working day (no weekend, no holiday). it that case it will be available in the next working date.

any ideas
thanks in advance
ids

[MatrixMan]

VBA is not necessary for this ... have a look at the NETWORKDAYS() formula. You would have to set up a list of holiday dates and refer to that in the third (optional) parameter. Whether it is cashed at the same or different bank is a simple IF statement.

Hope that helps. MM

[ids_73]

hi there
thanks for your initial support but it didnt help a lot.

and that because my English don’t help me to explain the problem.

what i want to mange to do is based on the following scenario:
i have some bank checks let say 5 of them
1)No 10 value 100 with payable date 15/5/2017
2)no 15 value 150 with payable date 15/5/2017
3)no 16 value 250 with payable date 27/5/2017
4)no 18 value 350 with payable date 27/5/2017
5)no 21 value 150 with payable date 26/5/2017

The bank gives the opportunity to cash in earlier date these checks but up to the amount of 100 the rest will be payed to me on the payable data
So if today is 14/5/2015 and I give to the bank the no 10&15 check the bank will pay me today 100 but the rest 150 will pay me on 15/5/2017
If I give to the bank on the 15/5/2017 the No16 then the bank will give me 100 and the rest 150 on 27/5/2017
If I give to the bank the no21&18 on 26/5/17 it will pay me only the value of the 26/5/2017 check 150 and the rest on 27/5/2017 which will be 500 (the rest of no16 and all of the 18)

I hope now to be more clear my problem

thank in advance for the help
IDS

[JeteMc]

It would help if you could put the scenario from post #3 into a spreadsheet with the amounts that are payable on certain days manually input. I also have a couple of questions:
1) Why do the dates in column A of the spreadsheet attached to post #1 include Saturdays and Sundays, since point 2 states no weekends, no holidays?
2) The dates in column E of the spreadsheet are labeled actual dates, is it correct to assume that these are what the first post refers to as payable dates?
3) IF (and this is a large if) a formula solution is possible, which may include multiple helper columns, would that be acceptable, or are you strictly seeking a VBA solution?
Let us know if you have any questions.

[JeteMc]

This proposed solution employs a formula as opposed to VBA. The dates in row one, starting with column I, are populated with the WORKDAY function. The formula that populates the table, H2:S22 is:

Please Login or Register  to view this content.

Let us know if you have any questions.