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

[Greene1999]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1
2
3		Cash Flow Forecast	Month		JAN
4			Week		1					2					3					4
5			Date		4-Jan	5-Jan	6-Jan	7-Jan	8-Jan	11-Jan	12-Jan	13-Jan	14-Jan	15-Jan	18-Jan	19-Jan	20-Jan	21-Jan	22-Jan	25-Jan	26-Jan	27-Jan	28-Jan	29-Jan
6
7		SALES
8		FACTORED SALES	Average Terms (Days)
9		GM	5		£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
10		FORD	5		£500	£500	£500	£500	£500	£700	£700	£700	£700	£700	£400	£400	£400	£400	£400	£600	£600	£600	£600	£600
11		FCA	10		£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
14		TOTAL 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
26		DIRECT CASH FLOW
27		CASH RECEIPTS	Average Terms (Days)
28		GM	5		£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
29		FORD	5		£0	£0	£0	£0	£0	£500	£500	£500	£500	£500	£700	£700	£700	£700	£700	£400	£400	£400	£400	£400
30		FCA	10		£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
33		TOTAL 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
Range	Formula
J4,O4,T4	J4	=E4+1
L4	L4	=+J4+1
M4:N4,Q4:S4	M4	=+L4+1
P4	P4	=+#REF!+1
F5:I5,K5:N5,P5:S5,U5:X5	F5	=E5+1
J5,O5,T5	J5	=I5+3
E14:X14	E14	=SUM(E9:E13)
E28:X32	E28	=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:X33	E33	=+SUM(E28:E32)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C28:C32	Cell Value	=0	text	NO

 

[Toadstool]

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
	A	B
1	Holiday	Date
2	MLK Day	1/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.

Greene1999.xlsx
	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
4	1					2					3					4
5	04-Jan-21	05-Jan-21	06-Jan-21	07-Jan-21	08-Jan-21	11-Jan-21	12-Jan-21	13-Jan-21	14-Jan-21	15-Jan-21	19-Jan-21	20-Jan-21	21-Jan-21	22-Jan-21	25-Jan-21	26-Jan-21	27-Jan-21	28-Jan-21	29-Jan-21
Calendar

Cell Formulas
Range	Formula
J4,T4,O4	J4	=E4+1
F5:W5	F5	=WORKDAY(E5,1,Holidays!$B$2:$B$15)

 

[Greene1999]

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
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
3	Cash Flow Forecast	Month		JAN
4		Week		1					2					3				4
5		Date		4-Jan	5-Jan	6-Jan	7-Jan	8-Jan	11-Jan	12-Jan	13-Jan	14-Jan	15-Jan	19-Jan	20-Jan	21-Jan	22-Jan	25-Jan	26-Jan	27-Jan	28-Jan	29-Jan
6
7	SALES
8	FACTORED SALES	Average Terms (Days)
9	GM	10		£1,000	£1,000	£1,000	£1,000	£1,000
10
11
12
13
14	TOTAL 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
26	DIRECT CASH FLOW
27	CASH RECEIPTS	Average Terms (Days)
28	GM	10		£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
33	TOTAL 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
Range	Formula
J4,O4,T4	J4	=E4+1
L4	L4	=+J4+1
M4:N4	M4	=+L4+1
S4	S4	=O4+1
F5	F5	=WORKDAY(E5,1,Holidays!$B$2:$B$15)
G5:W5	G5	=WORKDAY(F5,1,Holidays!$A$3)
E14:W14	E14	=SUM(E9:E13)
E28:W28	E28	=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:W33	E33	=+SUM(E28:E32)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C28:C32	Cell Value	=0	text	NO

 

[Toadstool]

So does using Holiday dates in that formula address the challenge?

=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)

 

[Greene1999]

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?

 

[Toadstool]

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?

 

[Toadstool]

...and it seems you had the Holidays referenced incorrectly.

Does this do what you would expect?

Greene1999.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1
2
3		Cash Flow Forecast	Month		JAN
4			Week		1					2					3					4
5			Date		04-Jan-21	05-Jan-21	06-Jan-21	07-Jan-21	08-Jan-21	11-Jan-21	12-Jan-21	13-Jan-21	14-Jan-21	15-Jan-21	19-Jan-21	20-Jan-21	21-Jan-21	22-Jan-21	25-Jan-21	26-Jan-21	27-Jan-21	28-Jan-21	29-Jan-21	01-Feb-21
6
7		SALES
8		FACTORED SALES	Average Terms (Days)
9		GM	5		1000	1000	1000	1000	1000	1200	1200	1200	1200	1200	1400	1400	1400	1400	1400	1600	1600	1600	1600	1600
10		FORD	5		500	500	500	500	500	700	700	700	700	700	400	400	400	400	400	600	600	600	600	600
11		FCA	10		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
14		TOTAL FACTORED SALES			1900	1900	1900	1900	1900	2200	2200	2200	2200	2200	2040	2040	2040	2040	2040	2500	2500	2500	2500	2500
26		DIRECT CASH FLOW
27		CASH RECEIPTS	Average Terms (Days)
28		GM	5		0	0	0	0	0	1000	1000	1000	1000	1000	1200	1200	1200	1200	1200	1400	1400	1400	1400	1400
29		FORD	5		0	0	0	0	0	500	500	500	500	500	700	700	700	700	700	400	400	400	400	400
30		FCA	10		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
33		TOTAL FACTORED COLLECTIONS		29500	0	0	0	0	0	1500	1500	1500	1500	1500	2300	2300	2300	2300	2300	2100	2100	2100	2100	2100
Sheet3

Cell Formulas
Range	Formula
J4,T4,O4	J4	=E4+1
F5:X5	F5	=WORKDAY(E5,1,Holidays!$B$2:$B$15)
E14:X14	E14	=SUM(E9:E13)
E28:X32	E28	=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)
D33	D33	=SUM(E33:X33)
E33:X33	E33	=+SUM(E28:E32)