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.
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.