Calculate time elapsed between project stages

[egrospeRP]

Hello Excel Experts,
Need some advise on how to calculate time elapsed between project stages. We have projects that goes through several stages (Initial, Interim, and final). Within each stage there are multiple sub stages that goes on that we track dates on, and that is easy to calculate. Where it gets complicated is calculating days elapsed between stages (eg. how long did it take from the finish of initial stage to the beginning of the interim stage). What makes it more complicated is that a project can go through "interim" stage multiple times.

Any ideas on how I can solve this challenge?

Project Name			Stage	Date Agency submits package	Date Consultant sends review to PP-EC	Date PP-EC sends review to Consultant	Date PP-EC/Consultant submits to Agency
40th Avenue			Initial	11/1/2021	12/9/2021	1/3/2022	1/31/2022
40th Avenue			Interim	3/1/2022	3/2/2022	3/3/2022	3/4/2022
40th Avenue			Interim	3/7/2022	3/8/2022	3/10/2022	3/11/2022
40th Avenue			Final	3/14/2022	3/15/2022	3/16/2022	3/17/2022
Decoto Road - Niles Sub			Initial	10/4/2021	11/3/2021	11/5/2021	11/8/2021
Decoto Road - Niles Sub			Interim	11/30/2021	12/9/2021	12/10/2021	12/10/2021
Decoto Road - Niles Sub			Interim	1/7/2022	1/28/2022	2/1/2022	2/2/2022
Fruitridge Road			Initial	2/10/2021	4/6/2021	4/7/2021	4/8/2021
Fruitridge Road			Interim	4/16/2021	4/30/2021	5/6/2021	5/7/2021
Fruitridge Road			Interim	5/31/2021	6/15/2021	6/16/2021	6/18/2021
Fruitridge Road			Interim	7/1/2021	7/21/2021	7/22/2021	7/22/2021
Fruitridge Road			Interim	8/2/2021	8/6/2021	8/6/2021	8/9/2021
Fruitridge Road			Final	9/7/2021	9/30/2021	10/1/2021	10/1/2021

 

[egrospeRP]

Hoping someone could help.

 

[Soledad]

If your data table sorted by column A then column D then column E, you can try the formula in cell I2 then copied down

=IF(IFERROR(MATCH(D2,{"Initial";"Interim";"final"},0),0)>1,IF(COUNT(1/(A2<>"")/(A2=A$2:A2)/(D2=D$2:D2))=1,MIN(IF(A2=A$2:A2,IF(D2=D$2:D2,E$2:E2)))-MAX(IF(A2=A$2:A2,IF(INDEX({"Initial";"Interim";"final"},MATCH(D2,{"Initial";"Interim";"final"},0)-1)=D$2:D2,H$2:H2))),""),"")

Array formula, hit with key-combination Ctrl+Shift+Enter instead of just pressing Enter

 

[Soledad]

Note that data table must be sorted by column A then column E then column D instead of by column A then D then E as you apply the formula above.

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

What are the expected results for that sample data and how do you get them manually?

 

[egrospeRP]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

What are the expected results for that sample data and how do you get them manually?

Hi Peter,
Thanks for the suggestion on account details set up, I just updated my profile.

So the expected result is to calculate number of days elapsed between the stages of the project (for example, 20 days from Initial to Interim, 15 days from Interim 1 to Interim 2, 30 days from Interim 2 to Final). "Date PP-EC/Consultant Submits to Agency" is considered the end of each stage for that specific project (column A).

As mentioned above, the caveat is there could be multiple interim stages. As with the data, they are coming from a SharePoint list that are being populated into the spreadsheet. There will be no data entry on the spreadsheet.

 

[Peter_SSs]

For the expected results I was hoping ..
- To see the layout of the results, and
- Details of how you got them (or at least some of them) manually. For example you have said "20 days from Initial to Interim". Which project in the example are you talking about? How did you get the 20 days (that is, which cells/values did you use to get that and what was the actual calculation)?

 

[egrospeRP]

For the expected results I was hoping ..
- To see the layout of the results, and
- Details of how you got them (or at least some of them) manually. For example you have said "20 days from Initial to Interim". Which project in the example are you talking about? How did you get the 20 days (that is, which cells/values did you use to get that and what was the actual calculation)?

Hi Peter_SSs,

Thanks for your response. Please see detail below. This is essentially what I'm trying to accomplish.

• For example, on the project 40th avenue, the "days elapsed between stages" cell (G2) will be blank because it is the first stage of the review process of that project.
• The "interim" will calculate the "Date PP-EC submits to Agency" date of the interim stage minus the "Date PP-EC submits to Agency" date of the initial stage and will go on until the final stage.
• Note that a project can go through multiple interim stages but will only go through the initial and final stages once.

I hope this detail is enough.

Book1
	A	B	C	D	E	F	G
1	Project Name	Stage	Date Agency submits package	Date Consultant sends review to PP-EC	Date PP-EC sends review to Consultant	Date PP-EC submits to Agency	Days Elapsed between stages
2	40th Avenue	Initial	11/1/2021	12/9/2021	1/3/2022	1/31/2022	-
3	40th Avenue	Interim	3/1/2022	3/2/2022	3/3/2022	3/4/2022	32
4	40th Avenue	Interim	3/7/2022	3/8/2022	3/10/2022	3/11/2022	7
5	40th Avenue	Final	3/14/2022	3/15/2022	3/16/2022	3/17/2022	6
6	Decoto Road - Niles Sub	Initial	10/4/2021	11/3/2021	11/5/2021	11/8/2021	-
7	Decoto Road - Niles Sub	Interim	11/30/2021	12/9/2021	12/10/2021	12/10/2021	32
8	Decoto Road - Niles Sub	Interim	1/7/2022	1/28/2022	2/1/2022	2/2/2022	54
9	Fruitridge Road	Initial	2/10/2021	4/6/2021	4/7/2021	4/8/2021	-
10	Fruitridge Road	Interim	4/16/2021	4/30/2021	5/6/2021	5/7/2021	29
11	Fruitridge Road	Interim	5/31/2021	6/15/2021	6/16/2021	6/18/2021	42
12	Fruitridge Road	Interim	7/1/2021	7/21/2021	7/22/2021	7/22/2021	34
13	Fruitridge Road	Interim	8/2/2021	8/6/2021	8/6/2021	8/9/2021	18
14	Fruitridge Road	Final	9/7/2021	9/30/2021	10/1/2021	10/1/2021	53
query (20)

Cell Formulas
Range	Formula
G3:G5,G10:G14,G7:G8	G3	=F3-F2

 

[Peter_SSs]

So if that shows your expected results then you just need a formula adjustment to insert the "-" at the start of each project?

egrospeRP.xlsm
	A	B				F	G
1	Project Name	Stage				Date PP-EC submits to Agency	Days Elapsed between stages
2	40th Avenue	Initial				31/01/2022	-
3	40th Avenue	Interim				4/03/2022	32
4	40th Avenue	Interim				11/03/2022	7
5	40th Avenue	Final				17/03/2022	6
6	Decoto Road - Niles Sub	Initial				8/11/2021	-
7	Decoto Road - Niles Sub	Interim				10/12/2021	32
8	Decoto Road - Niles Sub	Interim				2/02/2022	54
9	Fruitridge Road	Initial				8/04/2021	-
10	Fruitridge Road	Interim				7/05/2021	29
11	Fruitridge Road	Interim				18/06/2021	42
12	Fruitridge Road	Interim				22/07/2021	34
13	Fruitridge Road	Interim				9/08/2021	18
14	Fruitridge Road	Final				1/10/2021	53
Sheet1

Cell Formulas
Range	Formula
G2:G14	G2	=IF(A2=A1,F2-F1,"-")