Days between dates in rows

[Jacobusn]

Hi
i want to calculate days between two dates if the serial number in a row is repeated in another row
can any one help me please

 

[PeteWright]

Hi @Jacobusn
Could you give us some sample data so we know what exactly you need?

 

[Jacobusn]

hi Pete
i have extracted the below from a report as the actual report is very large with multiple reference numbers.
from the below yuo can see i have a unique serial number. i have call dates and i have ticket numbers. i want to know what is the days between the call dates.
The data comes in rowes from the system and cant be changed to coloumns as the report will be to big
need a formula that looks at the unique serial number then deduact the dates from each other and places as per the manula examples in the last coloumn

can you help

Call Reference	Serial No	Call Date	Days since previous date device was serviced
GCN0501836​	UMZ01728​	2023-06-14 10:12:57​
GCN0511762​	UMZ01728​	2023-07-04 08:15:38​	20​
GCN0516946​	UMZ01728​	2023-07-12 12:19:23​	8​
GCN0521730​	UMZ01728​	2023-07-20 13:39:26​	8​

 

[PeteWright]

Hi @Jacobusn

Assuming the following:

1. Your data is sorted by "Serial No" and then by "Call Date"
2. "Call Date" is a text string exactly formatted as in your example
3. Your table is located in cells A1 to B5

Then the following formula would apply to the first cell of "Days since ...":

= IF($B1=$B2; DATEVALUE(LEFT($C2;10)) - DATEVALUE(LEFT($C1;10)); "")

If your data is not sorted, things get complex and beyond my knowledge.

One simple trick would be to sort your Excel sheet by those two columns using the Data tab on the ribbon.

Please let me know a little more on your data and if the formula helps.

 

[Jacobusn]

Hi @Jacobusn

Assuming the following:

1. Your data is sorted by "Serial No" and then by "Call Date"
2. "Call Date" is a text string exactly formatted as in your example
3. Your table is located in cells A1 to B5

Then the following formula would apply to the first cell of "Days since ...":

= IF($B1=$B2; DATEVALUE(LEFT($C2;10)) - DATEVALUE(LEFT($C1;10)); "")

If your data is not sorted, things get complex and beyond my knowledge.

One simple trick would be to sort your Excel sheet by those two columns using the Data tab on the ribbon.

Please let me know a little more on your data and if the formula helps.

 

[Jacobusn]

Hi Pete
i will it a shot and let you know
sorting the data is not an issue, that’s doable
will the formula still work if some serials have 4 entries and some serial 2 Entries
thx

 

[Jacobusn]

hi Pete
You a super star. Thanks it works beatifully.

 

[PeteWright]

hi Pete
You a super star. Thanks it works beatifully.

Thanks!
Glad I could help.