How to stop counting days when using DATEIF Funtion to Count days between two dates

[Marcie Be]

I was originally just counting the numbers of days between two dates. An earlier date and today.
The below formula did this for me
T4==IF($S4="","",DATEDIF($S4,IF($U5>=0,TODAY()),"D"))

Now in $U5 when I enter a completion date, I want the count in cell T4 to stop as the task is completed.
How does my formula above need to be modified to stop counting?

 

[Dave Patton]

Try either of the following

T202007a.xlsm
	S	T	U
3
4	31-Dec-19	91
5			31-Mar-20
6	31-Dec-19	91
7			31-Mar-20
8
1b

Cell Formulas
Range	Formula
T4	T4	=IF($S4="","",DATEDIF($S4,MIN($U5,TODAY()),"D"))
T6	T6	=(S6>0)*MIN($U7,TODAY())-S6

 

[Marcie Be]

Try either of the following

T202007a.xlsm
	S	T	U
3
4	31-Dec-19	91
5			31-Mar-20
6	31-Dec-19	91
7			31-Mar-20
8
1b

Cell Formulas
Range	Formula
T4	T4	=IF($S4="","",DATEDIF($S4,MIN($U5,TODAY()),"D"))
T6	T6	=(S6>0)*MIN($U7,TODAY())-S6

Thank you Dave

I have made the modification, I will confirm tomorrow that the count has stop as of the completion date.
Can I ask, how the replacement of IF with MIN and the removal of $U5>=0, wil stop the count once a completion date is entered into $U5?
I like to understand the logic and learn

 

[Marcie Be]

Try either of the following

T202007a.xlsm
	S	T	U
3
4	31-Dec-19	91
5			31-Mar-20
6	31-Dec-19	91
7			31-Mar-20
8
1b

Cell Formulas
Range	Formula
T4	T4	=IF($S4="","",DATEDIF($S4,MIN($U5,TODAY()),"D"))
T6	T6	=(S6>0)*MIN($U7,TODAY())-S6

@Dave Patton, I have confirmed the revised formula below works. I had dates in the cells S4 and U5 and S4 was still counting using todays's date. That has stopped now uing the new formula and counted only up until the completion date was entered.

T4	=IF($S4="","",DATEDIF($S4,MIN($U5,TODAY()),"D"))

Thanks again, much appreciated

Best Regards

Marcie Be

 

[Dave Patton]

Thanks for the feedback.

T202007a.xlsm
	S	T	U
4	43830	91
5			43921
6
1b

Cell Formulas
Range	Formula
T4	T4	=(Start>0)*(MIN(Completion,TODAY())-S4)

Named Ranges
Name	Refers To	Cells
Completion	='1b'!$U$5	T4
Start	='1b'!$S$4	T4

T202007a.xlsm
	S	T	U	V	W	X	Y	Z	AA
7	T4	=IF(Start="","",DATEDIF(Start,MIN($U5,TODAY()),"D"))
8	T4	=(Start>0)*MIN(Completion,TODAY())-S4
9
10		This post shows the Dates in General format; the format doesn't affect calculations.
11		It is often useful to review formulas with Excel's Formulas Evaluate Formula
12		True = 1 False = 0
13		Start 43830>0 True
14		Min of Completion 43921 and Today 44018 is 43921 therefore
15		43921-43830 =91
1b