Formula adding # days & adjust if result is weekend holi

[mardeebo]

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.

 

[mardeebo]

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]

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]

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)