Duration (Time Difference) between 2 date fields in different rows

[bannianke]

I highly appreciate a helpful tip how to calculate the Status Duration of example provided below :

Scenario:

A case with a case number can have different case path statuses :

• Open
• Taken into account
• Updated by Customer / BU : PoS
• Customer waiting
• Reopened
• Closed
• Point of Sales waiting

Every time the Case Path gets changed from "Old Value" to "New Value" a record for this change is presented in the raw data.
So I have for 1 case several records.
The download is sorting the records by "Edit" date oldest to newest but not in combination with the Case Number .... it looks at all cases and sorts only the Edit date.

I need to show how long the case was in each Status.
The Edit Date would reference the Date when the Status changed from the old to New Value
So I need to Compare 2 Edit Dates sitting in different rows to get the time duration
In addition the first reference DAte for status "Open" is always the Date when the Case was Created"

For Example :
case 4970052 stayed in Status "Open" for 5 Days before it changed to Status "Taken into account" ... in this status the case stayed for 1 Day... in Status "Customer Waiting" the duration was 3 days , in status "Updated by Customer / BU : PoS" for 5 days etc.

Which formula would work to show the duration for each status ?
Thanks a lot for your assistance !
Much appreciate it

bye
Anke

Case Number	Case Created Date	Field / Event	Edit Date	Status - Old Value	Status - New Value	Status Duration
4967490	29/08/2023	Case Path	29/08/2023 12:04	Open	Taken into account
4967490	29/08/2023	Case Path	29/08/2023 12:22	Taken into account	Updated by Customer / BU : PoS
4967490	29/08/2023	Case Path	29/08/2023 16:03	Updated by Customer / BU : PoS	Closed
4970052	25/08/23	Case Path	30/08/2023 16:51	Open	Taken into account	5 days
4970052	29/08/2023	Case Path	31/08/2023 17:35	Taken into account	Customer waiting	1 day
4970052	29/08/2023	Case Path	03/09/23 11:12	Customer waiting	Updated by Customer / BU : PoS	3 days
4984107	04/09/2023	Case Path	04/09/2023 16:08	Open	Taken into account
4984107	04/09/2023	Case Path	04/09/2023 16:38	Taken into account	Closed
4970052	29/08/2023	Case Path	08/09/2023 16:21	Updated by Customer / BU : PoS	Point of Sales waiting	5 days
4970052	29/08/2023	Case Path	18/09/2023 17:18	Point of Sales waiting	Updated by Customer / BU : PoS	10 days
4970052	29/08/2023	Case Path	19/09/2023 09:47	Updated by Customer / BU : PoS	Point of Sales waiting	1 day

 

[Leo Skywalker]

Hi there,

Assuming your data starts in A1, the Sheet name is Sheet1, and that the "Open" status is the first one for each case, then, try the following formula in G2:

=IF(Sheet1!$E2="Open",INT(Sheet1!$D2-Sheet1!$B2),INT(Sheet1!$D2)-INT(LOOKUP(2,1/(Sheet1!$A2=$A$1:A1),$D$1:D1)))&" day(s)"

Copy down.

 

[bannianke]

Hi Leo, Perfect ...that was exactly what I was looking for.
Tried your suggested formula and it works like a dream !!!
Thanks again
bye
Anke

 

[Leo Skywalker]

Glad to help!!

Please, mark the post as solution.

Mark as Solution

The helpers at the MrExcel community enjoy providing answers to your Excel questions, but we need to know that we're actually helping you. To aid in this, we decided to use the new 'Mark as solution' functionality. This allows the Original Poster (OP) of a thread to mark the solution post...

www.mrexcel.com