Need help with days remaining formula

[Iccreamann21]

I am looking for a better formula in column H that does not include weekends as a day remaining. H7 and H24 are examples

IATF READINESS 2024.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	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ
3										June 24, 2024						July 01, 2024							July 08, 2024							July 15, 2024
4									7.7777777777777300	24-Jun	25-Jun	26-Jun	27-Jun	28-Jun	29-Jun	30-Jun	1-Jul	2-Jul	3-Jul	4-Jul	5-Jul	6-Jul	7-Jul	8-Jul	9-Jul	10-Jul	11-Jul	12-Jul	13-Jul	14-Jul	15-Jul	16-Jul	17-Jul	18-Jul	19-Jul	20-Jul
5	DONE	TASK	AUDITOR	OWNER	START DATE	END DATE	DAYS DURATION	DAYS REMAINING	DONE %	MO	TU	WE	TH	FR	SA	SU	MO	TU	WE	TH	FR	SA	SU	MO	TU	WE	TH	FR	SA	SU	MO	TU	WE	TH	FR	SA
6	UPDATE PROCESS MAPPING
7		PLANT	AUDITOR #1	OWNER #1	6/25/2024	7/18/2024	18	7	56%																									«
8		MANAGEMENT REVIEW	AUDITOR #2	OWNER #2	6/25/2024	6/26/2024	2	0	100%			«
9		PROCESS #1	AUDITOR #3	OWNER #3	6/25/2024	6/26/2024	2	0	100%			«
10		PROCESS #2	AUDITOR #4	OWNER #4	6/26/2024	6/27/2024	2	0	100%				«
11		PROCESS #3	AUDITOR #5	OWNER #5	6/26/2024	6/27/2024	2	0	100%				«
12		PROCESS #4	AUDITOR #6	OWNER #6	7/2/2024	7/3/2024	2	0	100%										«
13		PROCESS #5	AUDITOR #7	OWNER #7	7/2/2024	7/3/2024	2	0	100%										«
14		PROCESS #6	AUDITOR #8	OWNER #8	7/9/2024	7/10/2024	2	0	100%																	«
15		PROCESS #7	AUDITOR #9	OWNER #9	7/9/2024	7/10/2024	2	0	100%																	«
16		PROCESS #8	AUDITOR #10	OWNER #10	7/10/2024	7/11/2024	2	0	100%																		«
17		PROCESS #9	AUDITOR #11	OWNER #11	7/10/2024	7/11/2024	2	0	50%																		«
18		PROCESS #10	AUDITOR #12	OWNER #12			0	0	0%
19		PROCESS #11	AUDITOR #13	OWNER #13			0	0	0%
20		PROCESS #12	AUDITOR #14	OWNER #14			0	0	0%
21		PROCESS #13	AUDITOR #15	OWNER #15	7/15/2024	7/16/2024	2	5	0%																							«
22		PROCESS #14	AUDITOR #16	OWNER #16	7/16/2024	7/17/2024	2	6	0%																								«
23		PROCESS #15	AUDITOR #17	OWNER #17	7/17/2024	7/18/2024	2	7	0%																									«
24		PROCESS #16	AUDITOR #18	OWNER #18	7/12/2024	7/15/2024	2	4	0%																						«
OH - PLANT READINESS 2024 (2)

Cell Formulas
Range	Formula
J3	J3	=C1
P3	P3	=J3+7
W3,AD3	W3	=P3+7
J4	J4	=C1
K4:AJ4	K4	=J4+1
J6,J7:AJ24	J6	=IF(J$4=$F6,"«","")
E7	E7	=MIN(E8:F24)
F7	F7	=MAX(E8:F24)
G7:G24	G7	=NETWORKDAYS(E7,F7)
H7:H24	H7	=IF(F7-TODAY() < 0.5,0,F7-TODAY())
I7	I7	=AVERAGE(I8:I24)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J25,J7:BE24,J6,J26:BE86,J88:BE91,J87,J93:BE96,J92	Expression	=WEEKDAY(J$4,2)>5	text	NO
I6:I96	Other Type	DataBar		NO
J25,J7:BE24,J6,J26:BE86,J88:BE91,J87,J93:BE96,J92	Expression	=AND($I6>0,J$4<=($E6+($F6-$E6)*$I6),J$4>=$E6)	text	NO
J25,J7:BE24,J6,J26:BE86,J88:BE91,J87,J93:BE96,J92	Expression	=AND(J$4>=$E6,J$4<=$F6)	text	NO
J25,J7:BE24,J6,J26:BE86,J88:BE91,J87,J93:BE96,J92	Expression	=J$4=TODAY()	text	NO

 

[Fluff]

How about

=LET(d,NETWORKDAYS(TODAY(),F7),IF(d<=0,0,d))

 

[Iccreamann21]

How about

=LET(d,NETWORKDAYS(TODAY(),F7),IF(d<=0,0,d))

Thank you! That worked perfectly!

What does the "d" represent for future knowledge?

 

[Vogateer]

Thank you! That worked perfectly!

What does the "d" represent for future knowledge?

The LET formula allows you to give a name to the results of the following formula, so it means whatever you want it to mean. In the formula you posted, you could enter "days" (or nearly any other word) for all the "d"s and it would still work. The LET formula is a way for you to avoid recalculating the same formula over and over again, making things more efficient while also making the code more readable.

 

[Fluff]

you could enter "days" (or nearly any other word)

Whilst that is true, I would advise against using the names of functions (such as Days) as it can cause confusion.

 

[Vogateer]

Whilst that is true, I would advise against using the names of functions (such as Days) as it can cause confusion.

Yes, good point!