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 |
= IF($B1=$B2; DATEVALUE(LEFT($C2;10)) - DATEVALUE(LEFT($C1;10)); "")
Hi @Jacobusn
Assuming the following:
Then the following formula would apply to the first cell of "Days since ...":
- Your data is sorted by "Serial No" and then by "Call Date"
- "Call Date" is a text string exactly formatted as in your example
- Your table is located in cells A1 to B5
Excel Formula:= 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.
Thanks!hi Pete
You a super star. Thanks it works beatifully.