Calculate time elapsed between project stages

egrospeRP

New Member
Joined
Sep 5, 2018
Messages
15
Office Version
  1. 365
Platform
  1. Windows
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 NameStageDate Agency submits packageDate Consultant sends review to PP-EC Date PP-EC sends review to ConsultantDate PP-EC/Consultant submits to Agency
40th AvenueInitial11/1/202112/9/20211/3/20221/31/2022
40th AvenueInterim3/1/20223/2/20223/3/20223/4/2022
40th AvenueInterim3/7/20223/8/20223/10/20223/11/2022
40th AvenueFinal3/14/20223/15/20223/16/20223/17/2022
Decoto Road - Niles SubInitial10/4/202111/3/202111/5/202111/8/2021
Decoto Road - Niles SubInterim11/30/202112/9/202112/10/202112/10/2021
Decoto Road - Niles SubInterim1/7/20221/28/20222/1/20222/2/2022
Fruitridge RoadInitial2/10/20214/6/20214/7/20214/8/2021
Fruitridge RoadInterim4/16/20214/30/20215/6/20215/7/2021
Fruitridge RoadInterim5/31/20216/15/20216/16/20216/18/2021
Fruitridge RoadInterim7/1/20217/21/20217/22/20217/22/2021
Fruitridge RoadInterim8/2/20218/6/20218/6/20218/9/2021
Fruitridge RoadFinal9/7/20219/30/202110/1/202110/1/2021
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
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
Excel Formula:
=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
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Last edited:
Upvote 0
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)?
 
Upvote 0
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
ABCDEFG
1Project NameStageDate Agency submits packageDate Consultant sends review to PP-EC Date PP-EC sends review to ConsultantDate PP-EC submits to AgencyDays Elapsed between stages
240th AvenueInitial11/1/202112/9/20211/3/20221/31/2022-
340th AvenueInterim3/1/20223/2/20223/3/20223/4/202232
440th AvenueInterim3/7/20223/8/20223/10/20223/11/20227
540th AvenueFinal3/14/20223/15/20223/16/20223/17/20226
6Decoto Road - Niles SubInitial10/4/202111/3/202111/5/202111/8/2021-
7Decoto Road - Niles SubInterim11/30/202112/9/202112/10/202112/10/202132
8Decoto Road - Niles SubInterim1/7/20221/28/20222/1/20222/2/202254
9Fruitridge RoadInitial2/10/20214/6/20214/7/20214/8/2021-
10Fruitridge RoadInterim4/16/20214/30/20215/6/20215/7/202129
11Fruitridge RoadInterim5/31/20216/15/20216/16/20216/18/202142
12Fruitridge RoadInterim7/1/20217/21/20217/22/20217/22/202134
13Fruitridge RoadInterim8/2/20218/6/20218/6/20218/9/202118
14Fruitridge RoadFinal9/7/20219/30/202110/1/202110/1/202153
query (20)
Cell Formulas
RangeFormula
G3:G5,G10:G14,G7:G8G3=F3-F2
 
Upvote 0
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
ABFG
1Project NameStageDate PP-EC submits to AgencyDays Elapsed between stages
240th AvenueInitial31/01/2022-
340th AvenueInterim4/03/202232
440th AvenueInterim11/03/20227
540th AvenueFinal17/03/20226
6Decoto Road - Niles SubInitial8/11/2021-
7Decoto Road - Niles SubInterim10/12/202132
8Decoto Road - Niles SubInterim2/02/202254
9Fruitridge RoadInitial8/04/2021-
10Fruitridge RoadInterim7/05/202129
11Fruitridge RoadInterim18/06/202142
12Fruitridge RoadInterim22/07/202134
13Fruitridge RoadInterim9/08/202118
14Fruitridge RoadFinal1/10/202153
Sheet1
Cell Formulas
RangeFormula
G2:G14G2=IF(A2=A1,F2-F1,"-")
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,178
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top