Formula adding # days & adjust if result is weekend holi

mardeebo

New Member
Joined
Aug 13, 2004
Messages
39
Office Version
  1. 2016
Platform
  1. Windows
  2. Mobile
  3. Web
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
Joined
Aug 13, 2004
Messages
39
Office Version
  1. 2016
Platform
  1. Windows
  2. Mobile
  3. Web
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 need that day to jump to the 10th.

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
Joined
Aug 13, 2004
Messages
39
Office Version
  1. 2016
Platform
  1. Windows
  2. Mobile
  3. Web
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
Joined
Mar 23, 2005
Messages
20,825
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)
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top