How to fix this formula or add feature that skips over bank holidays

Greene1999

New Member
Joined
Jul 15, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hello everyone! I need some formula help here. Please see the attached simplified copy of my spreadsheet.

The top portion (rows 9-13) represents sales while the lower portion (rows 28-32) represents collections of payments. Column C (C9:C13) represents payment terms, that is how many days until the amounts in the sales section are collected. For example, cell E9 represents that there were £1,000 in sales to GM on Monday, Jan 4 with payment terms of 5 business days (cash cannot be collected on weekends as banks are closed). The formula in cell range E28:X32 serves to display the data in the sales section on the date that it is to be collected based on the payment terms in column C. So this £1,000 from GM will be collected in 5 business days on Monday, Jan 11.

The formula below is currently being used in cell E28, and is applied to cell range E28:X32.

=IFERROR(INDEX($E$9:$X$13,MATCH($B28,$B$9:$B$13,0),MATCH(WORKDAY(E$5,-INDEX($C$9:$C$13,MATCH($B28,$B$9:$B$13,0))),$E$5:$X$5,0)),0)

This is working perfectly, but does not take into consideration bank holidays, which are like weekends in this case when banks are closed and collections cannot take place. Thus, these days, which are enumerated in the "Holidays" tab, will need to be "skipped over," if you will. I do not know if this is possible to do through the formula or by adding additional functionality to the workbook. The only bank holiday included in the snippet of the workbook I have attached above is Jan 18.

Currently, cell range O28:O32 is displaying sales data from prior days (cells J9, J10, and E11 sales are being displayed in cells O28, O29, and O30, respectively). However, this is the issue I need resolved. Jan 18 is a bank holiday when collections cannot occur, so this data should actually be displayed on Tuesday, Jan 19, cell range P28:P32. Please let me know if you need additional details. Thanks!

Forecast.xlsx
ABCDEFGHIJKLMNOPQRSTUVWX
1
2
3Cash Flow ForecastMonthJAN
4Week1234
5Date4-Jan5-Jan6-Jan7-Jan8-Jan11-Jan12-Jan13-Jan14-Jan15-Jan18-Jan19-Jan20-Jan21-Jan22-Jan25-Jan26-Jan27-Jan28-Jan29-Jan
6
7SALES
8FACTORED SALESAverage Terms (Days)
9GM5£1,000£1,000£1,000£1,000£1,000£1,200£1,200£1,200£1,200£1,200£1,400£1,400£1,400£1,400£1,400£1,600£1,600£1,600£1,600£1,600
10FORD5£500£500£500£500£500£700£700£700£700£700£400£400£400£400£400£600£600£600£600£600
11FCA10£400£400£400£400£400£300£300£300£300£300£240£240£240£240£240£300£300£300£300£300
12£0£0£0£0£0£0£0£0£0£0£0£0£0£0£0£0£0£0£0£0
13£0£0£0£0£0£0£0£0£0£0£0£0£0£0£0£0£0£0£0£0
14TOTAL FACTORED SALES£1,900£1,900£1,900£1,900£1,900£2,200£2,200£2,200£2,200£2,200£2,040£2,040£2,040£2,040£2,040£2,500£2,500£2,500£2,500£2,500
15
25
26DIRECT CASH FLOW
27CASH RECEIPTSAverage Terms (Days)
28GM5£0£0£0£0£0£1,000£1,000£1,000£1,000£1,000£1,200£1,200£1,200£1,200£1,200£1,400£1,400£1,400£1,400£1,400
29FORD5£0£0£0£0£0£500£500£500£500£500£700£700£700£700£700£400£400£400£400£400
30FCA10£0£0£0£0£0£0£0£0£0£0£400£400£400£400£400£300£300£300£300£300
31£0£0£0£0£0£0£0£0£0£0£0£0£0£0£0£0£0£0£0£0
32£0£0£0£0£0£0£0£0£0£0£0£0£0£0£0£0£0£0£0£0
33TOTAL FACTORED COLLECTIONS£0£0£0£0£0£1,500£1,500£1,500£1,500£1,500£2,300£2,300£2,300£2,300£2,300£2,100£2,100£2,100£2,100£2,100
Calendar
Cell Formulas
RangeFormula
J4,O4,T4J4=E4+1
L4L4=+J4+1
M4:N4,Q4:S4M4=+L4+1
P4P4=+#REF!+1
F5:I5,K5:N5,P5:S5,U5:X5F5=E5+1
J5,O5,T5J5=I5+3
E14:X14E14=SUM(E9:E13)
E28:X32E28=IFERROR(INDEX($E$9:$X$13,MATCH($B28,$B$9:$B$13,0),MATCH(WORKDAY(E$5,-INDEX($C$9:$C$13,MATCH($B28,$B$9:$B$13,0))),$E$5:$X$5,0)),0)
E33:X33E33=+SUM(E28:E32)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C28:C32Cell Value=0textNO
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Hi Greene1999,

I'm not following the logic completely but it appears that formula uses the average terms to retrieve a value from rows 9 to 13. As you're dates in row 5 already omit weekends then why not just omit 18 January as an option?

So your Holidays tab would look something like this
Greene1999.xlsx
AB
1HolidayDate
2MLK Day1/18/2021
3
4
Holidays


Then the formula to display dates in row 5 would hardcode the first day then use
=WORKDAY(E5,1,Holidays!$B$2:$B$15)
copied right to omit weekends and holidays.

Cell Formulas
RangeFormula
J4,T4,O4J4=E4+1
F5:W5F5=WORKDAY(E5,1,Holidays!$B$2:$B$15)
 
Upvote 0
Hey! Thank you for that suggestion, that actually makes a lot of sense. I will just use the Holidays tab to omit all bank holidays and copy the formula to the right for the full year in the complete spreadsheet.

So, that works for the dates. However, this is causing a new issue with the formula in cell range E28:W32.

=IFERROR(INDEX($E$9:$W$13,MATCH($B28,$B$9:$B$13,0),MATCH(WORKDAY(E$5,-INDEX($C$9:$C$13,MATCH($B28,$B$9:$B$13,0))),$E$5:$W$5,0)),0)

The formula isn't messed up, it just needs to be adjusted to work with the new (omitted dates). Here's what's happening (incorrectly) in the attached spreadsheet: Sales of £1,000 were made on Monday, Jan 4 (cell E9). With payment terms of 10 business days (cell C9), this £1,000 should be displayed 10 business days from Jan 4, which would be Monday, Jan 18, BUT this is a bank holiday, not a business day, so the amount should be showing up on Tuesday, Jan 19 (cell O28). Instead, the amount is not showing up at all anymore. Additionally, the Friday, Jan 8 sales (cell I9) should be displayed on Monday, Jan 25 (cell S28). Does this make sense? Let me know if you need any other info to solve this. Thanks!

Forecast.xlsx
BCDEFGHIJKLMNOPQRSTUVW
3Cash Flow ForecastMonthJAN
4Week1234
5Date4-Jan5-Jan6-Jan7-Jan8-Jan11-Jan12-Jan13-Jan14-Jan15-Jan19-Jan20-Jan21-Jan22-Jan25-Jan26-Jan27-Jan28-Jan29-Jan
6
7SALES
8FACTORED SALESAverage Terms (Days)
9GM10£1,000£1,000£1,000£1,000£1,000
10
11
12
13
14TOTAL FACTORED SALES£1,000£1,000£1,000£1,000£1,000£0£0£0£0£0£0£0£0£0£0£0£0£0£0
15
25
26DIRECT CASH FLOW
27CASH RECEIPTSAverage Terms (Days)
28GM10£0£0£0£0£0£0£0£0£0£0£1,000£1,000£1,000£1,000£0£0£0£0£0
29
30
31
32
33TOTAL FACTORED COLLECTIONS£0£0£0£0£0£0£0£0£0£0£1,000£1,000£1,000£1,000£0£0£0£0£0
trial 1
Cell Formulas
RangeFormula
J4,O4,T4J4=E4+1
L4L4=+J4+1
M4:N4M4=+L4+1
S4S4=O4+1
F5F5=WORKDAY(E5,1,Holidays!$B$2:$B$15)
G5:W5G5=WORKDAY(F5,1,Holidays!$A$3)
E14:W14E14=SUM(E9:E13)
E28:W28E28=IFERROR(INDEX($E$9:$W$13,MATCH($B28,$B$9:$B$13,0),MATCH(WORKDAY(E$5,-INDEX($C$9:$C$13,MATCH($B28,$B$9:$B$13,0))),$E$5:$W$5,0)),0)
E33:W33E33=+SUM(E28:E32)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C28:C32Cell Value=0textNO
 
Upvote 0
So does using Holiday dates in that formula address the challenge?
Excel Formula:
=IFERROR(INDEX($E$9:$X$13,MATCH($B28,$B$9:$B$13,0),MATCH(WORKDAY(E$5,-INDEX($C$9:$C$13,MATCH($B28,$B$9:$B$13,0)),Holidays!$B$2:$B$15),$E$5:$X$5,0)),0)
 
Upvote 0
For some reason, no it does not. I would think that should work, but I am left with the same issue as before. I think it would have to be some kind of adjustment in the Index and Match part of the formula, but I'm not sure. I'm not that smart lol. Do you have any other recommendations? Even if it's not an addition to the formula, but maybe another feature I could add to the spreadsheet that would allow the formula to "skip over" these holidays?
 
Upvote 0
It would seem that if you follow both my suggestions then you wouldn't see 18 Jan and it wouldn't use it to calculate, so it should work.

Where do the numbers come from in row 9, 10 and 11? I see it picks the terms from column C for the matching make but E9 to X13 are manually entered so how are those numbers decided?
...and in the first example 18 Jan had 1400 entered so was that in error?
 
Upvote 0
...and it seems you had the Holidays referenced incorrectly.

Does this do what you would expect?

Greene1999.xlsx
ABCDEFGHIJKLMNOPQRSTUVWX
1
2
3Cash Flow ForecastMonthJAN
4Week1234
5Date04-Jan-2105-Jan-2106-Jan-2107-Jan-2108-Jan-2111-Jan-2112-Jan-2113-Jan-2114-Jan-2115-Jan-2119-Jan-2120-Jan-2121-Jan-2122-Jan-2125-Jan-2126-Jan-2127-Jan-2128-Jan-2129-Jan-2101-Feb-21
6
7SALES
8FACTORED SALESAverage Terms (Days)
9GM510001000100010001000120012001200120012001400140014001400140016001600160016001600
10FORD5500500500500500700700700700700400400400400400600600600600600
11FCA10400400400400400300300300300300240240240240240300300300300300
1200000000000000000000
1300000000000000000000
14TOTAL FACTORED SALES19001900190019001900220022002200220022002040204020402040204025002500250025002500
26DIRECT CASH FLOW
27CASH RECEIPTSAverage Terms (Days)
28GM500000100010001000100010001200120012001200120014001400140014001400
29FORD500000500500500500500700700700700700400400400400400
30FCA100000000000400400400400400300300300300300
3100000000000000000000
3200000000000000000000
33TOTAL FACTORED COLLECTIONS2950000000150015001500150015002300230023002300230021002100210021002100
Sheet3
Cell Formulas
RangeFormula
J4,T4,O4J4=E4+1
F5:X5F5=WORKDAY(E5,1,Holidays!$B$2:$B$15)
E14:X14E14=SUM(E9:E13)
E28:X32E28=IFERROR(INDEX($E$9:$X$13,MATCH($B28,$B$9:$B$13,0),MATCH(WORKDAY(E$5,-INDEX($C$9:$C$13,MATCH($B28,$B$9:$B$13,0)),Holidays!$B$2:$B$15),$E$5:$X$5,0)),0)
D33D33=SUM(E33:X33)
E33:X33E33=+SUM(E28:E32)
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,267
Members
448,558
Latest member
aivin

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
Back
Top