Date durations with multiple criteria

[Aubreybake]

Hi!
I was trying to automate a column for duration (column E) between cycles (column D). Each cycle will have a discipline associated with it (Column B) and a date the cycle has been updated (column C). Each discipline also falls under a program (column A). Users of the spreadsheet will fill in columns A-D while E should automatically generate the duration since the last cycle update. The data input won’t be filtered so the program and discipline won’t be for an easy Datedif formula.

eg:
Program Discipline Date Cycle Duration
Shop 1 Permit 5/15/21 0 0
Shop 2 Design 8/10/21 0 0
Shop 1 Permit 5/30/21 1 15
Shop 3 Permit 9/05/21 0 0
Shop 2 Design 9/01/21 1 22
Shop 2 Permit 9/05/21 0 0

Sorry just typed this up on my phone and didn’t have access to the spreadsheet to share a snippet.

 

[etaf]

do you mean

The data input won’t be filtered so the program and discipline won’t be for an easy Datedif formula.

As you are using version 365 - then I have used a MAXIFS () to calculate the Date and then a datedif
Also a countifs()

Hopefully the sample you showed is representative all all possible data entry - otherwise the formula will not work , as the dates need to be ascending, for this to work

Book1
	A	B	C	D	E	F	G	H
1	Program	Discipline	Date	Cycle	Duration		Cycle	Duration
2	Shop 1	Permit	5/15/21	0	0		0	0
3	Shop 2	Design	8/10/21	0	0		0	0
4	Shop 1	Permit	5/30/21	1	15		1	15
5	Shop 3	Permit	9/5/21	0	0		0	0
6	Shop 2	Design	9/1/21	1	22		1	22
7	Shop 2	Permit	9/5/21	0	0		0	0
Sheet1

Cell Formulas
Range	Formula
G2:G7	G2	=COUNTIFS($A$2:A2,A2,$B$2:B2,B2)-1
H2:H7	H2	=IF(MAXIFS($C$1:C1,$A$1:A1,A2,$B$1:B1,B2)=0,0,DATEDIF(MAXIFS($C$1:C1,$A$1:A1,A2,$B$1:B1,B2),C2,"D"))