# Formula adding # days & adjust if result is weekend holi

#### mardeebo

##### New Member
I've been tinkering with the workday and weekday function but can't find the correct formula.

I'm reconcling credit card settlement dates and their corresponding bank deposit date. There is a delay between when they are processed on the terminal and when the bank gets the deposit - this date varies with the type of credit card used (ie Amex is 4 day delay, discover is 3 day delay).

It's not a straight forward add x number of business days. Rather, it's more like add 4 regular days, but if the result lands on a Sat/Sun/or holiday move it forward to the next business day.

For example, looking at Amex deposits in early October. No banks were open Oct 9. So, October 10 had 4 days worth of settlements in its deposits -

Oct 3, 4, 5, and 6.

Can someone help me with a formula that given a particular settlement date, I can get the tentative bank deposit date?

I truly appreciate it....

PS..I do have basic VBA skills (like creating my own code for excel macros) but it's all self-taught (from this board, no less!) If this would work better with a macro, I'm game.

PSS. Each day has approx 700 credit card transactions - I wish to put the bank settlement date in a column for each transaction. I figure I'll do the calculating in excel, then import the spreadsheet into access to create nice looking reports.

### Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

#### mardeebo

##### New Member
I'm a little bit closer...here's the formula I have now and it's performing correctly except if the weekday result is a holiday.

=IF(OR(WEEKDAY(A4+4)=1,WEEKDAY(A4+4)=7),WORKDAY(A4+4,1,Holidays),A4+4)

How can I add in another "OR" that would know if A4+4 is a holiday?

For example, Adding 4 to October 5 results in MOnday, Oct. 9 (a holiday). Because it's evaluting as FALSE for my IF statement, my result is October 9.

I do apologize if this has been asked before..I couldn't find anything that wasn't just a simply "use workday function" response in the archives

#### mardeebo

##### New Member
Talking to myself here...

I solved my problem. For anyone wanting to see the final formula, here it is

This formula adds a specific number of days, depending on Credit Card type, and adjusts forward 1 business day if the result lands on a weekend or holiday.

If anyone sees a potential problem with this, I'd surely appreciate the heads up.

=IF(B2="Amex",IF(OR(WEEKDAY(A2+4)=1,WEEKDAY(A2+4)=7,IF(ISNUMBER(MATCH(A2+4,Holidays,0)),1,0)),WORKDAY(A2+4,1,Holidays),A2+4),IF(OR(B2="Visa",B2="M/C"),IF(OR(WEEKDAY(A2+1)=1,WEEKDAY(A2+1)=7,IF(ISNUMBER(MATCH(A2+1,Holidays,0)),1,0)),WORKDAY(A2+1,1,Holidays),A2+1),IF(OR(WEEKDAY(A2+3)=1,WEEKDAY(A2+3)=7,IF(ISNUMBER(MATCH(A2+3,Holidays,0)),1,0)),WORKDAY(A2+3,1,Holidays),A2+3)))

Thanks,
Deb

#### barry houdini

##### MrExcel MVP
Hello Deb,

Sorry you didn't get any replies before

If I understand correctly then I don't think this has to be so complicated. In general if you wanted to add 4 days to a date in A2 but move it forward to the next workday if that results in a weekend or holiday then you can use

=WORKDAY(A2+4-1,1,holidays)

If you want to add 4 days for AMEX and 1 day for Visa or M/C and 3 days for all others then try

=WORKDAY(A2+IF(B2="Amex",4,IF(OR(B2="Visa",B2="M/C"),1,3))-1,1,holidays)

Replies
0
Views
165
Replies
1
Views
465
Replies
1
Views
336
Replies
5
Views
675
Replies
7
Views
2K

1,136,613
Messages
5,676,806
Members
419,652
Latest member
jjakub33

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back