Compute duration and miles-per-hour and total miles
 

How can I compute the duration and mph in each row?

After I move the other cells down one row, I insert the new day's info in the first row. How can I compute the total miles (of all rows)?

Compute duration and miles-per-hour and total miles
 

Miles Duration MPH Start time End time

7.84 10:35 AM 1:10 PM
7.28 11:35 AM 2:00 PM
7.18 11:10 AM 1:30 PM
6.60 1:10 PM 3:25 PM
8.98 10:35 AM 1:30 PM
7.41 12:30 PM 3:00 PM
7.69 1:10 PM 3:50 PM
7.67 11:55 AM 2:25 PM
8.64 10:35 AM 1:20 PM
7.24 11:30 AM 1:45 PM
8.85 11:25 AM 2:15 PM
7.11 11:12 AM 1:30 PM
9.04 10:05 AM 1:00 PM
6.70 10:05 AM 12:10 PM
8.42 10:55 AM 1:30 PM
7.61 11:05 AM 1:30 PM
8.17 10:20 AM 12:50 PM
8.60 10:55 AM 1:40 PM
7.73 12:43 PM 3:10 PM
6.63 10:00 AM 12:10 PM
7.03 10:40 AM 1:00 PM
8.75 10:40 AM 1:30 PM
7.90 12:40 PM 3:15 PM
7.41 10:20 AM 12:40 PM
6.75 1:00 PM 3:10 PM
7.63 11:20 AM 1:45 PM
7.18 11:20 AM 1:40 PM
7.70 12:00 PM 2:28 PM
7.12 10:45 AM 1:00 PM
7.58 11:05 AM 1:25 PM
7.73 12:45 PM 3:14 PM
5.80 10:20 AM 12:10 PM
6.68 10:20 AM 12:30 PM
6.68 9:30 AM 11:30 AM
7.45 10:00 AM 12:24 PM
7.20 11:20 AM 1:40 PM
6.82 11:27 AM 1:42 PM
7.16 1:35 PM 3:51 PM
6.67 10:30 AM 12:38 PM
6.48 11:55 AM 2:05 PM
5.79 10:05 AM 12:00 PM
6.64 12:00 PM 2:10 PM
8.72 10:00 AM 12:50 PM
5.78 11:55 AM 1:40 PM
6.78 12:45 PM 2:58 PM
7.14 12:45 PM 3:13 PM
7.83 10:30 AM 1:02 PM
5.78 11:30 AM 1:15 PM
8.25 5:20 PM 7:50 PM
5.76 6:20 PM 8:15 PM
6.78 5:35 PM 7:55 PM
6.60 5:55 PM 8:05 PM
7.30 6:55 PM 9:05 PM
5.90 6:50 PM 8:50 PM
7.30 6:25 PM 8:43 PM
6.78 6:10 PM 8:14 PM
8.39 6:20 PM 9:00 PM
6.10 7:00 PM 8:58 PM
6.09 7:15 PM 9:10 PM
7.10 7:15 PM 9:25 PM
7.21 6:50 PM 9:10 PM
6.78 6:50 PM 9:02 PM
5.76 7:20 PM 9:08 PM
6.20 7:25 PM 9:25 PM
6.20 7:05 PM 9:05 PM
5.76 7:45 PM 9:35 PM
7.20 7:55 PM 10:10 PM
5.40 7:55 PM 9:45 PM
4.76 7:35 PM 9:15 PM
7.25 7:15 PM 9:32 PM
6.40 8:10 PM 10:05 PM
6.20 7:15 PM 9:15 PM
5.20 7:40 PM 9:20 PM
6.25 8:04 PM 10:13 PM
5.67 7:45 PM 9:35 PM
6.34 7:20 PM 9:20 PM
4.80 7:40 PM 9:09 PM
6.21 7:35 PM 9:30 PM
5.22 7:25 PM 9:10 PM
5.22 6:25 PM 8:10 PM
7.57 6:20 PM 8:40 PM
6.25 7:20 PM 9:25 PM
6.22 6:55 PM 8:55 PM
6.09 6:55 PM 8:45 PM
6.09 6:45 PM 8:40 PM
6.58 6:55 PM 9:05 PM
5.15 7:45 PM 9:25 PM
6.09 7:40 PM 9:30 PM
6.09 7:25 PM 9:15 PM
4.82 7:55 PM 9:23 PM
6.42 6:25 PM 8:31 PM
7.57 6:25 PM 8:48 PM
6.20 6:45 PM 8:55 PM
7.71 6:21 PM 8:50 PM
4.76 6:06 PM 7:43 PM
7.20 7:35 PM 10:00 PM
5.06 7:30 PM 9:05 PM
5.72 7:30 PM 9:20 PM
6.60 7:00 PM 9:05 PM
6.61 5:35 PM 7:40 PM
6.60 6:36 PM 8:46 PM
5.71 6:26 PM 8:30 PM
7.68 6:20 PM 9:00 PM
6.60 6:55 PM 8:55 PM
7.10 7:00 PM 9:15 PM
6.26 6:30 PM 8:25 PM
4.81 7:55 PM 9:35 PM
7.20 6:40 PM 9:05 PM
6.60 7:00 PM 9:15 PM
4.81 6:05 PM 7:35 PM
4.81 6:40 PM 8:25 PM
7.97 6:20 PM 8:52 PM
7.26 6:20 PM 8:50 PM
5.91 6:20 PM 8:15 PM
6.19 6:15 PM 8:15 PM
7.16 6:35 PM 9:10 PM
6.12 6:55 PM 8:55 PM
4.81 6:15 PM 7:55 PM
8.12 5:55 PM 8:25 PM
5.51 6:35 PM 8:25 PM
4.81 6:00 PM 7:35 PM
7.40 6:40 PM 9:10 PM
3.06 8:15 PM 9:20 PM
6.60 6:15 PM 8:30 PM
4.81 7:15 PM 8:55 PM
5.72 6:05 PM 8:05 PM
7.40 6:20 PM 8:45 PM
5.72 6:15 PM 8:10 PM
7.20 5:45 PM 8:05 PM
5.22 6:10 PM 8:00 PM
6.00 6:10 PM 8:15 PM
5.80 6:05 PM 8:10 PM
5.22 6:05 PM 7:50 PM
4.81 6:30 PM 8:15 PM
6.60 6:25 PM 8:40 PM
4.81 6:30 PM 8:15 PM
5.48 6:05 PM 7:55 PM
4.81 6:30 PM 8:15 PM
3.50 6:45 PM 7:55 PM
4.81 6:30 PM 8:15 PM
2.02 5:05 PM 5:45 PM
4.81 6:30 PM 8:15 PM
2.02 5:20 PM 6:00 PM
3.96 5:00 PM 6:35 PM

Compute duration and miles-per-hour and total miles
 

You should be able to do this without VBA.

I am assuming your columns are as follows:
A = Miles
B = Duration
C = MPH
D = Start time
E = End time

Further, I assume that your headers are in row 1, and the data begins in row 2.

Finally, I assume that your values for start and end time are stored as time/date (as opposed to text).

In B2, to calculate Duration:
B2 =(D2E2)+E2-D2

In C2, to calculate the MPH:
C2 =A2/(B2*24)

Does that work?


On Monday, April 16, 2018 at 2:18:12 PM UTC-4, GARYWC wrote:
Miles Duration MPH Start time End time

7.84 10:35 AM 1:10 PM
7.28 11:35 AM 2:00 PM
7.18 11:10 AM 1:30 PM
6.60 1:10 PM 3:25 PM
8.98 10:35 AM 1:30 PM
7.41 12:30 PM 3:00 PM
7.69 1:10 PM 3:50 PM
7.67 11:55 AM 2:25 PM
8.64 10:35 AM 1:20 PM
7.24 11:30 AM 1:45 PM
8.85 11:25 AM 2:15 PM
7.11 11:12 AM 1:30 PM
9.04 10:05 AM 1:00 PM
6.70 10:05 AM 12:10 PM
8.42 10:55 AM 1:30 PM
7.61 11:05 AM 1:30 PM
8.17 10:20 AM 12:50 PM
8.60 10:55 AM 1:40 PM
7.73 12:43 PM 3:10 PM
6.63 10:00 AM 12:10 PM
7.03 10:40 AM 1:00 PM
8.75 10:40 AM 1:30 PM
7.90 12:40 PM 3:15 PM
7.41 10:20 AM 12:40 PM
6.75 1:00 PM 3:10 PM
7.63 11:20 AM 1:45 PM
7.18 11:20 AM 1:40 PM
7.70 12:00 PM 2:28 PM
7.12 10:45 AM 1:00 PM
7.58 11:05 AM 1:25 PM
7.73 12:45 PM 3:14 PM
5.80 10:20 AM 12:10 PM
6.68 10:20 AM 12:30 PM
6.68 9:30 AM 11:30 AM
7.45 10:00 AM 12:24 PM
7.20 11:20 AM 1:40 PM
6.82 11:27 AM 1:42 PM
7.16 1:35 PM 3:51 PM
6.67 10:30 AM 12:38 PM
6.48 11:55 AM 2:05 PM
5.79 10:05 AM 12:00 PM
6.64 12:00 PM 2:10 PM
8.72 10:00 AM 12:50 PM
5.78 11:55 AM 1:40 PM
6.78 12:45 PM 2:58 PM
7.14 12:45 PM 3:13 PM
7.83 10:30 AM 1:02 PM
5.78 11:30 AM 1:15 PM
8.25 5:20 PM 7:50 PM
5.76 6:20 PM 8:15 PM
6.78 5:35 PM 7:55 PM
6.60 5:55 PM 8:05 PM
7.30 6:55 PM 9:05 PM
5.90 6:50 PM 8:50 PM
7.30 6:25 PM 8:43 PM
6.78 6:10 PM 8:14 PM
8.39 6:20 PM 9:00 PM
6.10 7:00 PM 8:58 PM
6.09 7:15 PM 9:10 PM
7.10 7:15 PM 9:25 PM
7.21 6:50 PM 9:10 PM
6.78 6:50 PM 9:02 PM
5.76 7:20 PM 9:08 PM
6.20 7:25 PM 9:25 PM
6.20 7:05 PM 9:05 PM
5.76 7:45 PM 9:35 PM
7.20 7:55 PM 10:10 PM
5.40 7:55 PM 9:45 PM
4.76 7:35 PM 9:15 PM
7.25 7:15 PM 9:32 PM
6.40 8:10 PM 10:05 PM
6.20 7:15 PM 9:15 PM
5.20 7:40 PM 9:20 PM
6.25 8:04 PM 10:13 PM
5.67 7:45 PM 9:35 PM
6.34 7:20 PM 9:20 PM
4.80 7:40 PM 9:09 PM
6.21 7:35 PM 9:30 PM
5.22 7:25 PM 9:10 PM
5.22 6:25 PM 8:10 PM
7.57 6:20 PM 8:40 PM
6.25 7:20 PM 9:25 PM
6.22 6:55 PM 8:55 PM
6.09 6:55 PM 8:45 PM
6.09 6:45 PM 8:40 PM
6.58 6:55 PM 9:05 PM
5.15 7:45 PM 9:25 PM
6.09 7:40 PM 9:30 PM
6.09 7:25 PM 9:15 PM
4.82 7:55 PM 9:23 PM
6.42 6:25 PM 8:31 PM
7.57 6:25 PM 8:48 PM
6.20 6:45 PM 8:55 PM
7.71 6:21 PM 8:50 PM
4.76 6:06 PM 7:43 PM
7.20 7:35 PM 10:00 PM
5.06 7:30 PM 9:05 PM
5.72 7:30 PM 9:20 PM
6.60 7:00 PM 9:05 PM
6.61 5:35 PM 7:40 PM
6.60 6:36 PM 8:46 PM
5.71 6:26 PM 8:30 PM
7.68 6:20 PM 9:00 PM
6.60 6:55 PM 8:55 PM
7.10 7:00 PM 9:15 PM
6.26 6:30 PM 8:25 PM
4.81 7:55 PM 9:35 PM
7.20 6:40 PM 9:05 PM
6.60 7:00 PM 9:15 PM
4.81 6:05 PM 7:35 PM
4.81 6:40 PM 8:25 PM
7.97 6:20 PM 8:52 PM
7.26 6:20 PM 8:50 PM
5.91 6:20 PM 8:15 PM
6.19 6:15 PM 8:15 PM
7.16 6:35 PM 9:10 PM
6.12 6:55 PM 8:55 PM
4.81 6:15 PM 7:55 PM
8.12 5:55 PM 8:25 PM
5.51 6:35 PM 8:25 PM
4.81 6:00 PM 7:35 PM
7.40 6:40 PM 9:10 PM
3.06 8:15 PM 9:20 PM
6.60 6:15 PM 8:30 PM
4.81 7:15 PM 8:55 PM
5.72 6:05 PM 8:05 PM
7.40 6:20 PM 8:45 PM
5.72 6:15 PM 8:10 PM
7.20 5:45 PM 8:05 PM
5.22 6:10 PM 8:00 PM
6.00 6:10 PM 8:15 PM
5.80 6:05 PM 8:10 PM
5.22 6:05 PM 7:50 PM
4.81 6:30 PM 8:15 PM
6.60 6:25 PM 8:40 PM
4.81 6:30 PM 8:15 PM
5.48 6:05 PM 7:55 PM
4.81 6:30 PM 8:15 PM
3.50 6:45 PM 7:55 PM
4.81 6:30 PM 8:15 PM
2.02 5:05 PM 5:45 PM
4.81 6:30 PM 8:15 PM
2.02 5:20 PM 6:00 PM
3.96 5:00 PM 6:35 PM

Compute duration and miles-per-hour and total miles
 

That didn't work.
How can I send you my actual spreadsheet?

Compute duration and miles-per-hour and total miles
 

On Monday, 16 April 2018 06:55:25 UTC+1, GARYWC wrote:
How can I compute the duration and mph in each row?

After I move the other cells down one row, I insert the new day's info in the first row. How can I compute the total miles (of all rows)?

The solution given will work.
You just need to format column B as Time 00:00:00
and format column C as Number

You could also use the formula in column B of =MOD(E2-D2,1)

Compute duration and miles-per-hour and total miles
 

When I format B as Time 00:00:00, the displayed result is 2:55:00

How can I get the display to be 2 HRS 55 MINS?