difference between two dates with condition

[reymon2012]

Hello..i need your assistance.

i have two date columns in excel, 1st i need to get the difference between those dates, and based on the result i have additional condition.
if the difference is >=5 days prior to closing date the result needs to be 1, otherwise its 0 if completed inside 5 days prior to closing date.

Attached sample as reference. Any help much appreciated.

 

[Dave Patton]

Please check your criteria.
Consider

T202401a.xlsm
	A	B	C	D	E
1
2	Closing	Completed	Result	Result b	0 Within 5
3	06-12-23	01-12-23	1	1	0
4	10-12-23	01-12-23	1	1	1
5	03-12-23	01-12-23	0	0	0
6
1d

Cell Formulas
Range	Formula
C3:C5	C3	=--(ABS(B3-A3)>=5)
D3:D5	D3	=--(AND((B3-A3)<0,ABS(B3-A3)>=5))
E3:E5	E3	=--(AND((B3-A3)<0,ABS(B3-A3)>5))

 

[alansidman]

Book3
	A	B	C	D
2	clos ing date	Task Complete Date	Diff	Result
3	12/6/2023	12/1/2023	5	1
4	12/10/2023	12/1/2023	9	1
5	12/3/2023	12/1/2023	2	0
Sheet1

Cell Formulas
Range	Formula
C3:C5	C3	=A3-B3
D3:D5	D3	=IF(C3>=5,1,0)

 

[reymon2012]

Book3
	A	B	C	D
2	clos ing date	Task Complete Date	Diff	Result
3	12/6/2023	12/1/2023	5	1
4	12/10/2023	12/1/2023	9	1
5	12/3/2023	12/1/2023	2	0
Sheet1

Cell Formulas
Range	Formula
C3:C5	C3	=A3-B3
D3:D5	D3	=IF(C3>=5,1,0)

It works. and i also reversed your formula; the result is 1 if transaction is completed within days of the closing date.
However , i need to add another 2nd criteria based on the result of the 1st criteria.

IF Start date is within 5 days of the closing date, task needs to be completed within 1 business days from start date..

Can we combine both formula?

 

[Dave Patton]

This is a guess. Please explain clearly your requirement and post an example with expected results.
Try either or both of the suggestions.

T202401a.xlsm
	A	B	C	D	E
7	25-Dec-23	01-Dec-23	24	0	0
8	20-Dec-23	01-Dec-23	19	0	0
9	05-Dec-23	03-Dec-23	2	1	1
10
1d

Cell Formulas
Range	Formula
C7:C9	C7	=A7-B7
D7:D9	D7	=--(C7<5)
E7:E9	E7	=--(A3-B3<5)