Hours Between Dates

[colinh]

Hello All,

I need to calculate the hours of my vehicles. I have trackers but the information they give is just not quite what I need. the information they give me on a spreadsheet is in column C and is below. How can I use VBA to take the times in this case start at 09:57:38 and finish at 11:12:28 and in say cell A1 will be the total time taken. The tracker works on 24 hour clock and there is no set working weekday just dates etc.

DateTime

21/10/2020 09:57:38

21/10/2020 10:02:39

21/10/2020 10:07:39

21/10/2020 10:12:39

21/10/2020 10:17:39

21/10/2020 10:22:39

21/10/2020 10:27:40

21/10/2020 10:32:40

21/10/2020 10:37:31

21/10/2020 10:42:29

21/10/2020 10:47:27

21/10/2020 10:52:31

21/10/2020 10:57:32

21/10/2020 11:02:33

21/10/2020 11:07:30

21/10/2020 11:12:28

21/10/2020 11:17:31

 

[sandy666]

is that what you want?

DateTime		DateTime	Min	Max	Duration
21/10/2020 09:57		21/10/2020	09:57:38	11:17:31	0.01:19:53
21/10/2020 10:02
21/10/2020 10:07
21/10/2020 10:12
21/10/2020 10:17
21/10/2020 10:22
21/10/2020 10:27
21/10/2020 10:32
21/10/2020 10:37
21/10/2020 10:42
21/10/2020 10:47
21/10/2020 10:52
21/10/2020 10:57
21/10/2020 11:02
21/10/2020 11:07
21/10/2020 11:12
21/10/2020 11:17

 

[colinh]

Perfect thanks but the date time might be over one or more days, I have just shown a day but the vehicle might start say 22:00 on the 21/10/20 and travel into the next day at some point so the 22/10/20. Sorry

 

[sandy666]

so post representative example, use XL2BB to do it
or wait for someone else with vba

 

[colinh]

Hi Sorry
Has to get XL2BB up and running.

portugal Van - Horace.xlsx
	A	B	C
1
2		Status	DateTime
3	T1	Position	21/10/2020 09:57:38
4	T1	Position	21/10/2020 10:02:39
5	T1	Position	21/10/2020 10:07:39
6	T1	Position	21/10/2020 10:12:39
7	T1	Position	21/10/2020 10:17:39
8	T1	Position	21/10/2020 10:22:39
9	T1	Position	21/10/2020 10:27:40
10	T1	Position	21/10/2020 10:32:40
11	T1	Position	21/10/2020 10:37:31
12	T1	Position	21/10/2020 10:42:29
13	T1	Position	21/10/2020 10:47:27
14	T1	Position	21/10/2020 10:52:31
15	T1	Position	21/10/2020 10:57:32
16	T1	Position	21/10/2020 11:02:33
17	T1	Position	21/10/2020 11:07:30
18	T1	Position	21/10/2020 11:12:28
19	T1	Position	21/10/2020 11:17:31
20	T1	Position	21/10/2020 11:22:33
21	T1	Position	21/10/2020 11:28:02
22	T1	Position	21/10/2020 11:32:41
23	T1	Position	21/10/2020 11:37:30
24	T1	Stop	21/10/2020 11:38:06
25	T1	StopAlarm	21/10/2020 11:38:06
26	T1	Start	21/10/2020 11:51:49
27	T1	MotionAlarm	21/10/2020 11:51:49
28	T1	Position	21/10/2020 11:51:57
29	T1	Position	21/10/2020 11:56:52
30	T1	Position	21/10/2020 12:01:53
31	T1	Position	21/10/2020 12:06:51
32	T1	Stop	21/10/2020 12:09:08
33	T1	Start	21/10/2020 12:11:01
34	T1	Position	21/10/2020 12:11:05
35	T1	Position	21/10/2020 12:16:04
36	T1	Position	21/10/2020 12:21:03
37	T1	Position	21/10/2020 12:26:04
38	T1	Position	21/10/2020 12:31:32
39	T1	Position	21/10/2020 12:36:04
40	T1	Position	21/10/2020 12:41:20
41	T1	Position	21/10/2020 12:46:08
42	T1	Stop	21/10/2020 12:48:42
43	T1	Start	21/10/2020 12:55:33
44	T1	Position	21/10/2020 12:55:44
45	T1	Position	21/10/2020 13:00:36
46	T1	Position	21/10/2020 13:01:55
47	T1	Stop	21/10/2020 13:03:34
48	T1	Start	21/10/2020 13:03:53
49	T1	Position	21/10/2020 13:03:57
50	T1	Stop	21/10/2020 13:04:25
51	T1	Start	21/10/2020 13:06:29
52	T1	Position	21/10/2020 13:06:33
53	T1	Stop	21/10/2020 13:07:02
54	T1	Start	21/10/2020 13:13:10
55	T1	Position	21/10/2020 13:13:13
56	T1	Stop	21/10/2020 13:13:43
57	T1	Start	21/10/2020 13:16:59
58	T1	Position	21/10/2020 13:17:03
59	T1	Stop	21/10/2020 13:17:31
60	T1	Start	21/10/2020 13:38:42
61	T1	Position	21/10/2020 13:38:56
62	T1	Stop	21/10/2020 13:39:24
63	T1	Start	21/10/2020 13:46:54
64	T1	Position	21/10/2020 13:47:10
65	T1	Stop	21/10/2020 13:50:09
66	T1	Start	21/10/2020 14:00:10
67	T1	Position	21/10/2020 14:00:25
68	T1	Stop	21/10/2020 14:00:43
69	T1	Start	21/10/2020 14:02:08
70	T1	Position	21/10/2020 14:02:14
71	T1	Position	21/10/2020 14:07:36
72	T1	Stop	21/10/2020 14:07:54
73	T1	Start	21/10/2020 14:08:32
74	T1	Stop	21/10/2020 14:10:07
75	T1	Start	21/10/2020 14:10:41
76	T1	Stop	21/10/2020 14:11:53
77	T1	Start	21/10/2020 14:55:35
78	T1	Position	21/10/2020 14:56:45
79	T1	Position	21/10/2020 15:00:37
80	T1	Position	21/10/2020 15:05:37
81	T1	Position	21/10/2020 15:10:37
82	T1	Position	21/10/2020 15:16:01
83	T1	Position	21/10/2020 15:20:45
84	T1	Position	21/10/2020 15:25:39
85	T1	Position	21/10/2020 15:30:39
86	T1	Position	21/10/2020 15:36:05
87	T1	Position	21/10/2020 15:40:43
88	T1	Position	21/10/2020 15:46:11
89	T1	Position	21/10/2020 15:50:40
90	T1	Position	21/10/2020 15:55:42
91	T1	Position	21/10/2020 16:00:43
92	T1	Position	21/10/2020 16:05:40
93	T1	Position	21/10/2020 16:10:45
94	T1	Position	21/10/2020 16:15:41
95	T1	Position	21/10/2020 16:20:49
96	T1	Position	21/10/2020 16:25:42
97	T1	Position	21/10/2020 16:31:11
98	T1	Position	21/10/2020 16:35:42
99	T1	Position	21/10/2020 16:40:41
100	T1	Position	21/10/2020 16:45:40
101	T1	Position	21/10/2020 16:50:41
102	T1	Position	21/10/2020 16:55:42
103	T1	Position	21/10/2020 17:00:45
104	T1	Position	21/10/2020 17:05:47
105	T1	Position	21/10/2020 17:11:02
106	T1	Stop	21/10/2020 17:15:09
107	T1	Start	21/10/2020 17:15:44
108	T1	Position	21/10/2020 17:15:57
109	T1	Stop	21/10/2020 17:16:27
110	T1	Start	21/10/2020 17:16:49
111	T1	Position	21/10/2020 17:16:51
112	T1	Position	21/10/2020 17:22:05
113	T1	Position	21/10/2020 17:27:13
114	T1	Position	21/10/2020 17:31:52
115	T1	Stop	21/10/2020 17:33:35
116	T1	Start	21/10/2020 17:35:09
117	T1	Position	21/10/2020 17:35:11
118	T1	Stop	21/10/2020 17:35:41
119	T1	Start	21/10/2020 17:36:42
120	T1	Position	21/10/2020 17:36:44
121	T1	Position	21/10/2020 17:41:47
122	T1	Position	21/10/2020 17:46:48
123	T1	Position	21/10/2020 17:51:48
124	T1	Position	21/10/2020 17:57:07
125	T1	Position	21/10/2020 18:01:55
126	T1	Position	21/10/2020 18:06:46
127	T1	Position	21/10/2020 18:11:45
128	T1	Position	21/10/2020 18:16:58
129	T1	Position	21/10/2020 18:21:59
130	T1	Position	21/10/2020 18:27:00
131	T1	Position	21/10/2020 18:31:59
132	T1	Position	21/10/2020 18:37:11
133	T1	Position	21/10/2020 18:41:46
134	T1	Position	21/10/2020 18:46:45
135	T1	Position	21/10/2020 18:51:46
136	T1	Position	21/10/2020 18:56:55
137	T1	Position	21/10/2020 19:01:45
138	T1	Position	21/10/2020 19:06:45
139	T1	Position	21/10/2020 19:11:46
140	T1	Stop	21/10/2020 19:16:10
141	T1	Start	21/10/2020 19:23:03
142	T1	Position	21/10/2020 19:23:29
143	T1	Stop	21/10/2020 19:23:36
144	T1	Start	21/10/2020 19:33:13
145	T1	Position	21/10/2020 19:33:37
146	T1	Position	21/10/2020 19:38:42
147	T1	Position	21/10/2020 19:43:20
148	T1	Position	21/10/2020 19:48:17
149	T1	Stop	21/10/2020 19:53:31
150	T1	Start	21/10/2020 19:54:35
151	T1	Position	21/10/2020 19:54:38
152	T1	Position	21/10/2020 19:59:42
153	T1	Position	21/10/2020 20:04:41
154	T1	Position	21/10/2020 20:10:01
155	T1	Position	21/10/2020 20:14:38
156	T1	Position	21/10/2020 20:19:41
157	T1	Position	21/10/2020 20:24:38
158	T1	Position	21/10/2020 20:29:39
159	T1	Position	21/10/2020 20:34:39
160	T1	Position	21/10/2020 20:39:52
161	T1	Position	21/10/2020 20:42:05
162	T1	Position	21/10/2020 20:47:13
163	T1	Position	21/10/2020 20:52:31
164	T1	Position	21/10/2020 20:57:04
165	T1	Position	21/10/2020 21:02:04
166	T1	Position	21/10/2020 21:07:09
167	T1	Position	21/10/2020 21:12:04
168	T1	Position	21/10/2020 21:17:03
169	T1	Position	21/10/2020 21:22:09
170	T1	Position	21/10/2020 21:27:04
171	T1	Position	21/10/2020 21:32:07
172	T1	Position	21/10/2020 21:37:17
173	T1	Position	21/10/2020 21:42:04
174	T1	Position	21/10/2020 21:47:17
175	T1	Position	21/10/2020 21:52:20
176	T1	Position	21/10/2020 21:57:16
177	T1	Position	21/10/2020 22:02:07
178	T1	Position	21/10/2020 22:07:15
179	T1	Position	21/10/2020 22:12:08
180	T1	Position	21/10/2020 22:17:33
181	T1	Stop	21/10/2020 22:19:08
182	T1	Start	21/10/2020 22:22:09
183	T1	Position	21/10/2020 22:22:26
184	T1	Stop	21/10/2020 22:22:41
185	T1	Start	21/10/2020 22:30:29
186	T1	Position	21/10/2020 22:31:11
187	T1	Position	21/10/2020 22:35:33
188	T1	Position	21/10/2020 22:40:33
189	T1	Position	21/10/2020 22:45:32
190	T1	Position	21/10/2020 22:50:40
191	T1	Position	21/10/2020 22:55:32
192	T1	Position	21/10/2020 23:00:34
193	T1	Position	21/10/2020 23:05:36
194	T1	Position	21/10/2020 23:10:39
195	T1	Position	21/10/2020 23:15:44
196	T1	Position	21/10/2020 23:21:09
197	T1	Position	21/10/2020 23:25:41
198	T1	Position	21/10/2020 23:31:11
199	T1	Position	21/10/2020 23:35:37
200	T1	Position	21/10/2020 23:40:40
201	T1	Position	21/10/2020 23:45:40
202	T1	Position	21/10/2020 23:50:47
203	T1	Position	21/10/2020 23:55:43
204	T1	Position	22/10/2020 00:01:06
205	T1	Position	22/10/2020 00:05:49
206	T1	Position	22/10/2020 00:10:46
207	T1	Position	22/10/2020 00:16:11
208	T1	Position	22/10/2020 00:20:41
209	T1	Position	22/10/2020 00:26:00
210	T1	Position	22/10/2020 00:30:37
211	T1	Position	22/10/2020 00:35:37
212	T1	Position	22/10/2020 00:40:38
213	T1	Position	22/10/2020 00:45:49
214	T1	Position	22/10/2020 00:51:11
215	T1	Position	22/10/2020 00:55:41
216	T1	Position	22/10/2020 02:00:42
217	T1	Position	22/10/2020 02:05:52
218	T1	Stop	22/10/2020 02:09:01
219	T1	Start	22/10/2020 02:22:57
220	T1	Position	22/10/2020 02:23:05
221	T1	Position	22/10/2020 02:28:03
222	T1	Position	22/10/2020 02:33:07
223	T1	Position	22/10/2020 02:38:02
224	T1	Position	22/10/2020 02:43:08
225	T1	Position	22/10/2020 02:48:11
226	T1	Position	22/10/2020 02:53:01
227	T1	Position	22/10/2020 02:58:06
228	T1	Position	22/10/2020 03:03:15
229	T1	Position	22/10/2020 03:08:09
230	T1	Position	22/10/2020 03:13:18
231	T1	Position	22/10/2020 03:18:32
232	T1	Position	22/10/2020 03:23:01
233	T1	Stop	22/10/2020 03:24:43
234	T1	Start	22/10/2020 03:31:59
235	T1	Position	22/10/2020 03:32:06
236	T1	Position	22/10/2020 03:37:03
237	T1	Position	22/10/2020 03:42:02
238	T1	Stop	22/10/2020 03:46:33
239	T1	Start	22/10/2020 03:46:36
240	T1	Position	22/10/2020 03:46:42
241	T1	Position	22/10/2020 03:51:43
242	T1	Position	22/10/2020 03:56:41
243	T1	Position	22/10/2020 04:02:00
244	T1	Stop	22/10/2020 04:06:41
245	T1	Start	22/10/2020 04:07:33
246	T1	Position	22/10/2020 04:07:44
247	T1	Position	22/10/2020 04:12:39
248	T1	Position	22/10/2020 04:17:45
249	T1	Position	22/10/2020 04:23:00
250	T1	Position	22/10/2020 04:28:11
251	T1	Position	22/10/2020 04:32:38
252	T1	Position	22/10/2020 04:37:35
253	T1	Position	22/10/2020 04:42:37
254	T1	Position	22/10/2020 04:48:14
255	T1	Position	22/10/2020 04:53:03
256	T1	Position	22/10/2020 04:57:57
257	T1	Position	22/10/2020 05:02:59
258	T1	Position	22/10/2020 05:08:54
259	T1	Position	22/10/2020 05:12:39
260	T1	Position	22/10/2020 05:17:38
261	T1	Position	22/10/2020 05:22:46
262	T1	Position	22/10/2020 05:28:01
263	T1	Position	22/10/2020 05:32:41
264	T1	Position	22/10/2020 05:37:40
265	T1	Stop	22/10/2020 05:40:14
266	T1	Start	22/10/2020 06:37:32
267	T1	Position	22/10/2020 06:37:40
268	T1	Stop	22/10/2020 06:38:04
269	T1	Start	22/10/2020 06:39:01
270	T1	Position	22/10/2020 06:39:13
271	T1	Position	22/10/2020 06:44:06
272	T1	Position	22/10/2020 06:49:05
273	T1	Position	22/10/2020 06:54:03
274	T1	Position	22/10/2020 06:59:16
275	T1	Position	22/10/2020 07:04:16
276	T1	Position	22/10/2020 07:09:16
277	T1	Position	22/10/2020 07:14:16
278	T1	Position	22/10/2020 07:19:17
279	T1	Position	22/10/2020 07:24:04
280	T1	Position	22/10/2020 07:29:19
281	T1	Position	22/10/2020 07:34:17
282	T1	Position	22/10/2020 07:39:15
283	T1	Position	22/10/2020 07:44:07
284	T1	Position	22/10/2020 07:49:11
285	T1	Position	22/10/2020 07:54:14
286	T1	Position	22/10/2020 07:59:32
287	T1	Stop	22/10/2020 08:00:54
288	T1	Start	22/10/2020 08:06:22
289	T1	Position	22/10/2020 08:06:24
290	T1	Position	22/10/2020 08:11:56
291	T1	Position	22/10/2020 08:16:26
292	T1	Position	22/10/2020 08:21:28
293	T1	Position	22/10/2020 08:26:25
294	T1	Position	22/10/2020 08:31:25
295	T1	Position	22/10/2020 08:36:31
296	T1	Position	22/10/2020 08:41:28
297	T1	Position	22/10/2020 08:46:27
298	T1	Position	22/10/2020 08:51:28
299	T1	Position	22/10/2020 08:56:55
300	T1	Position	22/10/2020 09:01:41
301	T1	Position	22/10/2020 09:06:33
302	T1	Position	22/10/2020 09:11:29
303	T1	Position	22/10/2020 09:16:39
304	T1	Position	22/10/2020 09:21:32
305	T1	Position	22/10/2020 09:26:41
306	T1	Position	22/10/2020 09:31:28
307	T1	Position	22/10/2020 09:36:28
308	T1	Position	22/10/2020 09:41:41
309	T1	Position	22/10/2020 09:46:38
310	T1	Position	22/10/2020 09:51:40
311	T1	Position	22/10/2020 09:56:34
Report

 

[sandy666]

there is no any description about T column and Status column so I did this

DateTime	Min	Max	Duration
21/10/2020	09:57:38	23:55:43	0.13:58:05
22/10/2020	00:01:06	09:56:34	0.09:55:28

 

[colinh]

That's perfect thank you so much I have copied it into my spreadsheet and clicking like mad on the cells but I cant see the formula. sorry I have never used EX2BB before.

 

[sandy666]

you used XL2BB properly
but green table I did with Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    TypeLocale = Table.TransformColumnTypes(Source, {{"Received Date Gleaned From#(lf)Get Outlook Metadata.xlsm", type datetime}}, "en-US"),
    Date = Table.AddColumn(TypeLocale, "Date", each DateTime.Date([#"Received Date Gleaned From#(lf)Get Outlook Metadata.xlsm"]), type date),
    Time = Table.AddColumn(Date, "Time", each DateTime.Time([#"Received Date Gleaned From#(lf)Get Outlook Metadata.xlsm"]), type time)
in
    Time

as I said for vba or formula you'll need to wait for someone else