Subtraction of timesteps as long as ordernumber the same

Y

youneskil

New Member
Joined
Sep 22, 2021
Messages
11
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hey guys, I need your help.
I got two types of ordernumbers which are sorted alphabetically (scroll down) and appropriate time stamps.
Now I want that to calculate the delta_time stamp as long as the ordernumber is the same.
The first delta_time stamp of each ordernumber type is 00:00:00. Then subtract the second time stamp from the first to get the second delta_time stamp.
I filled the first two delta_time cells so that you understand.
This is just an example. In reality I got more than 100 ordernumber types. So I need an automated solution.
Thank you for your help!


ordernumbertimedelta_time
ZSC2JT2 VN07:03:0000:00:00
ZSC2JT2 VN07:05:4800:02:48
ZSC2JT2 VN07:07:29
ZSC2JT2 VN07:09:07
ZSC2JT2 VN07:10:34
ZSC2JT2 VN07:11:59
ZSC2JT2 VN07:14:38
ZSC2JT2 VN07:16:46
ZSC2JT2 VN07:18:14
ZSC2JT2 VN07:19:46
ZSC2JT2 VN07:21:35
ZSC2JT2 VN07:23:05
ZSC2JT2 VN07:24:27
ZSC2JT2 VN07:25:35
ZSC2JT2 VN07:27:41
ZSC2JT2 VN07:29:06
ZSC2JT2 VN07:48:27
ZSC2JT2 VN07:49:45
ZSC2JT2 VN07:51:09
ZSC2JT2 VN07:52:30
ZSC2JT2 VN07:54:38
ZSC2JT2 VN07:56:22
ZSC2JT2 VN07:57:47
ZSC2JT2 VN07:58:59
ZSC2JT2 VN08:00:24
ZSC2JT2 VN08:01:39
ZSC2JT2 VN08:02:51
ZSC2JT2 VN08:05:28
ZSC2JY6 VN06:32:04
ZSC2JY6 VN06:33:31
ZSC2JY6 VN06:34:44
ZSC2JY6 VN06:36:02
ZSC2JY6 VN06:37:23
ZSC2JY6 VN06:39:28
ZSC2JY6 VN06:40:41
ZSC2JY6 VN06:41:54
ZSC2JY6 VN06:43:17
ZSC2JY6 VN06:50:56
ZSC2JY6 VN06:52:10
ZSC2JY6 VN06:53:24
ZSC2JY6 VN06:54:37
ZSC2JY6 VN06:55:49
ZSC2JY6 VN06:57:02
ZSC2JY6 VN06:58:16
ZSC2JY6 VN07:04:07
ZSC2JY6 VN07:05:26
ZSC2JY6 VN07:06:40
ZSC2JY6 VN07:07:53
ZSC2JY6 VN07:09:06
ZSC2JY6 VN07:13:29
ZSC2JY6 VN07:15:45
ZSC2JY6 VN07:16:56
ZSC2JY6 VN07:18:14
ZSC2JY6 VN07:19:25
ZSC2JY6 VN07:20:41
ZSC2JY6 VN07:22:06
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Sort by date Sort by votes
Welcome to MrExcel Message Board.
Also change formula column Number format to [h]:mm:ss
Try this:
Book1
ABCDE
1ordernumbertimedelta_timedelta_time
2ZSC2JT2 VN7:03:000:00:000:00:00
3ZSC2JT2 VN7:05:480:02:480:02:48
4ZSC2JT2 VN7:07:290:01:41
5ZSC2JT2 VN7:09:070:01:38
6ZSC2JT2 VN7:10:340:01:27
7ZSC2JT2 VN7:11:590:01:25
8ZSC2JT2 VN7:14:380:02:39
9ZSC2JT2 VN7:16:460:02:08
10ZSC2JT2 VN7:18:140:01:28
11ZSC2JT2 VN7:19:460:01:32
12ZSC2JT2 VN7:21:350:01:49
13ZSC2JT2 VN7:23:050:01:30
14ZSC2JT2 VN7:24:270:01:22
15ZSC2JT2 VN7:25:350:01:08
16ZSC2JT2 VN7:27:410:02:06
17ZSC2JT2 VN7:29:060:01:25
18ZSC2JT2 VN7:48:270:19:21
19ZSC2JT2 VN7:49:450:01:18
20ZSC2JT2 VN7:51:090:01:24
21ZSC2JT2 VN7:52:300:01:21
22ZSC2JT2 VN7:54:380:02:08
23ZSC2JT2 VN7:56:220:01:44
24ZSC2JT2 VN7:57:470:01:25
25ZSC2JT2 VN7:58:590:01:12
26ZSC2JT2 VN8:00:240:01:25
27ZSC2JT2 VN8:01:390:01:15
28ZSC2JT2 VN8:02:510:01:12
29ZSC2JT2 VN8:05:280:02:37
30ZSC2JY6 VN6:32:040:00:00
31ZSC2JY6 VN6:33:310:01:27
32ZSC2JY6 VN6:34:440:01:13
33ZSC2JY6 VN6:36:020:01:18
34ZSC2JY6 VN6:37:230:01:21
35ZSC2JY6 VN6:39:280:02:05
36ZSC2JY6 VN6:40:410:01:13
37ZSC2JY6 VN6:41:540:01:13
38ZSC2JY6 VN6:43:170:01:23
39ZSC2JY6 VN6:50:560:07:39
40ZSC2JY6 VN6:52:100:01:14
41ZSC2JY6 VN6:53:240:01:14
42ZSC2JY6 VN6:54:370:01:13
43ZSC2JY6 VN6:55:490:01:12
44ZSC2JY6 VN6:57:020:01:13
45ZSC2JY6 VN6:58:160:01:14
46ZSC2JY6 VN7:04:070:05:51
47ZSC2JY6 VN7:05:260:01:19
48ZSC2JY6 VN7:06:400:01:14
49ZSC2JY6 VN7:07:530:01:13
50ZSC2JY6 VN7:09:060:01:13
51ZSC2JY6 VN7:13:290:04:23
52ZSC2JY6 VN7:15:450:02:16
53ZSC2JY6 VN7:16:560:01:11
54ZSC2JY6 VN7:18:140:01:18
55ZSC2JY6 VN7:19:250:01:11
56ZSC2JY6 VN7:20:410:01:16
57ZSC2JY6 VN7:22:060:01:25
58
Sheet1
Cell Formulas
RangeFormula
D2:D57D2=IF(AND(A2=A1,B2>B1),B2-B1,0)
 
Upvote 0
Solution
Hello Maabadi, thanks for your response!
When I enter your formular in my D2 cell my Excel does not recognise that it is a formular. I get a fail report.
 
Upvote 0
Nevermind, I got it. My excel does not understand English terms like "if" and "AND". had to use German. Now it works. Thank you!
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question
Excel Pivot table Time Values is not populating same as Source Sheet
Replies
1
Views
337
awoohaw
awoohaw
J
  • Solved
Excel "Strava Best Effort" Calculation
2
Replies
13
Views
455
felixstraube
F
T
  • Solved
Date of Max value
Replies
5
Views
210
Fluff
Fluff
M
  • Solved
Formula Help.
Replies
3
Views
119
Fluff
Fluff
S
  • Solved
How to Highlight Month-end dates
Replies
3
Views
393
kevin9999
K

Forum statistics

Threads
1,215,734
Messages
6,126,542
Members
449,316
Latest member
sravya

Share this page

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