Calculate number of days given certain criteria

[sanjorge06]

Good day, hopefully I can get some help as I am not an Excel avid user. I am trying to calculate the number of days between 2 dates if certain criteria is met. As seen in the pic, Column B contains Order Numbers, each order number has multiple rows as each process step has a different sequence number (Column H). Each process step for each order has 3 dates, Last activity (Column L), Create date (Column M), and Due Date (Column N). For Step Numbers 10, 15, or 20 I need to calculate the amount of days between the "Create Date" (Column M) and "Last Activity" (Column L) and return the result in column O. For every other Step Number (Not 10, 15, or 20) I need to calculate the days between each step's Last Activity (Rows) up to the last row containing the same Order Number. If a new order number appears in a row, it will contain the Step Number 10, 15, or 20 and must calculate as described above.

The file contains 448 different order numbers and 5981 rows. Is there a way to create a formula to include all criteria and copy from row 2 to 5981?

Thank you

 

[gelu]

Hello SJ,

I hope I understood correctly the second condition.
Used dummy dates - please use xl2bb addin next time (XL2BB - Excel Range to BBCode) so we can copy paste your numbers in Excel.

No of Days.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
4
5	Order No	Step No	Last Activity	Create Date	Due Date	=If + Days	=If + Days		Cond 1	Cond Status		Cond 2	Same order
6													0
7	70020680	10	2022-01-16	2022-01-08	2022-01-31	8			10	TRUE		FALSE	1
8	70020680	40	2022-01-17	2022-01-08	2022-01-31	-	1		15	FALSE		TRUE	1
9	70020680	50	2022-01-18	2022-01-08	2022-01-31	-	1		20	FALSE		TRUE	1
10	70020680	60	2022-01-19	2022-01-08	2022-01-31	-	1			FALSE		TRUE	1
11	70020680	70	2022-01-20	2022-01-08	2022-01-31	-	1			FALSE		TRUE	1
12	70020680	90	2022-01-21	2022-01-08	2022-01-31	-	1			FALSE		TRUE	1
13	70020680	110	2022-01-22	2022-01-08	2022-01-31	-	1			FALSE		TRUE	1
14	70020680	120	2022-01-23	2022-01-08	2022-01-31	-	1			FALSE		TRUE	1
15	70020680	160	2022-01-24	2022-01-08	2022-01-31	-	1			FALSE		TRUE	1
16	70020680	170	2022-01-25	2022-01-08	2022-01-31	-	1			FALSE		TRUE	1
17	70020680	180	2022-01-26	2022-01-08	2022-01-31	-	1			FALSE		TRUE	1
18	70020680	190	2022-01-27	2022-01-08	2022-01-31	-	1			FALSE		TRUE	1
19	70020682	15	2022-01-28	2022-01-08	2022-01-26	20				TRUE		FALSE	2
20	70020682	40	2022-01-29	2022-01-08	2022-01-26	-	1			FALSE		TRUE	2
21	70020682	50	2022-01-30	2022-01-08	2022-01-26	-	1			FALSE		TRUE	2
22	70020682	60	2022-01-31	2022-01-08	2022-01-26	-	1			FALSE		TRUE	2
23	70020682	90	2022-02-01	2022-01-08	2022-01-26	-	1			FALSE		TRUE	2
24	70020682	120	2022-02-02	2022-01-08	2022-01-26	-	1			FALSE		TRUE	2
25	70020682	140	2022-02-03	2022-01-08	2022-01-26	-	1			FALSE		TRUE	2
26	70020682	170	2022-02-04	2022-01-08	2022-01-26	-	1			FALSE		TRUE	2
27	70020682	220	2022-02-05	2022-01-08	2022-01-26	-	1			FALSE		TRUE	2
28	70020682	260	2022-02-06	2022-01-08	2022-01-26	-	1			FALSE		TRUE	2
29	70020682	280	2022-02-07	2022-01-08	2022-01-26	-	1			FALSE		TRUE	2
30	70020685	15	2022-02-08	2022-01-08	2022-01-26	31				TRUE		FALSE	3
Sheet1

Cell Formulas
Range	Formula
C7	C7	=D7+8
F7:F30	F7	=IF(J7=TRUE, DAYS(C7,D7),"-")
G7:G30	G7	=IF(L7=TRUE,DAYS(C8,C7),"")
L7:L30	L7	=AND(B7<>$I$7,B7<>$I$8,B7<>$I$9)
M7:M30	M7	=IF(A7=A6,M6,M6+1)
C8:C30	C8	=C7+1
D20:E30,D19,D8:E18	D8	=D7
E19	E19	=E18-5
J7:J30	J7	=OR(B7=$I$7,B7=$I$8,B7=$I$9)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
L7:L30	Cell Value	=FALSE	text	NO
J7:J30	Cell Value	=TRUE	text	NO