Fill in missing dates for weekends, holidays and source errors

[DRSteele]

Can someone please help me?

I wish to fill in the missing data for cells where there is a weekend, a holiday or an NA error. I need a formula to get it done. Like this, where the blue stuff in columnO I filled in from the prior valid date by typing it:

Book1
	M	N	O	P	Q	R
11	DATA
12	date	value	desired1		date	desired2
13	12/23/2022	$ 19,506.65	$ 19,506.65		12/23/2022	$ 19,506.65
14	12/27/2022	#N/A	$ 19,506.65		12/24/2022	$ 19,506.65
15	12/28/2022	$ 19,284.10	$ 19,284.10		12/25/2022	$ 19,506.65
16	12/29/2022	$ 19,485.89	$ 19,485.89		12/26/2022	$ 19,506.65
17	12/30/2022	$ 19,384.92	$ 19,384.92		12/27/2022	$ 19,506.65
18	1/2/2023	#N/A	$ 19,384.92		12/28/2022	$ 19,284.10
19	1/3/2023	#N/A	$ 19,384.92		12/29/2022	$ 19,485.89
20	1/4/2023	$ 19,588.83	$ 19,588.83		12/30/2022	$ 19,384.92
21	1/5/2023	$ 19,506.84	$ 19,506.84		12/31/2022	$ 19,384.92
22	1/6/2023	$ 19,814.51	$ 19,814.51		1/1/2023	$ 19,384.92
23	1/9/2023	$ 19,857.07	$ 19,857.07		1/2/2023	$ 19,384.92
24	1/10/2023	$ 19,898.86	$ 19,898.86		1/3/2023	$ 19,384.92
25					1/4/2023	$ 19,588.83
26					1/5/2023	$ 19,506.84
27					1/6/2023	$ 19,814.51
28					1/7/2023	$ 19,814.51
29					1/8/2023	$ 19,814.51
30					1/9/2023	$ 19,857.07
31					1/10/2023	$ 19,898.86
32					1/11/2023	$ 19,898.86
Sheet1

Cell Formulas
Range	Formula
N14,N18:N19	N14	=NA()
R13:R32	R13	=XLOOKUP(Q13,$M$13:$M$24,$O$13:$O$24,,-1)

 

[Eric W]

Try:

Book1
	K	L	M	N	O	P	Q	R
11			DATA
12	Holidays		date	value	desired1		date	desired2
13	2/2/2023		12/23/2022	19506.65	19506.65		12/23/2022	19506.65
14			12/27/2022	#N/A	19506.65		12/24/2022	19506.65
15			12/28/2022	19284.10	19284.10		12/25/2022	19506.65
16			12/29/2022	19485.89	19485.89		12/26/2022	19506.65
17			12/30/2022	19384.92	19384.92		12/27/2022	19506.65
18			1/2/2023	#N/A	19384.92		12/28/2022	19284.10
19			1/3/2023	#N/A	19384.92		12/29/2022	19485.89
20			1/4/2023	19588.83	19588.83		12/30/2022	19384.92
21			1/5/2023	19506.84	19506.84		12/31/2022	19384.92
22			1/6/2023	19814.51	19814.51		1/1/2023	19384.92
23			1/9/2023	19857.07	19857.07		1/2/2023	19384.92
24			1/10/2023	19898.86	19898.86		1/3/2023	19384.92
25							1/4/2023	19588.83
26							1/5/2023	19506.84
27							1/6/2023	19814.51
28							1/7/2023	19814.51
29							1/8/2023	19814.51
30							1/9/2023	19857.07
31							1/10/2023	19898.86
32							1/11/2023	19898.86
Sheet2

Cell Formulas
Range	Formula
N14,N18:N19	N14	=NA()
O13:O24	O13	=IF(WORKDAY(M13-1,1,$K$13:$K$15)=M13,IFERROR(N13,O12),O12)
R13:R32	R13	=XLOOKUP(Q13,$M$13:$M$24,$O$13:$O$24,,-1)

 

[DRSteele]

Thanks Eric. That helps a lot.