Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Compute duration and miles-per-hour and total miles
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 80
Default 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)?
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 80
Default 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
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3
Default 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

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 80
Default Compute duration and miles-per-hour and total miles
That didn't work.
How can I send you my actual spreadsheet?

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 18
Default 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)
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 80
Default 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?

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Miles Calculation? Ewing25 Excel Programming 3 April 29th 08 03:21 PM
Does anyone now how to calculate miles per hour using excel? RuSLMaN Excel Worksheet Functions 2 March 4th 08 02:36 AM
Miles Per Hour WalkerDude999 Excel Worksheet Functions 1 May 7th 06 09:42 AM
How to use miles per hour and mileage to estimate travel time OhioSnowBird13 Excel Worksheet Functions 1 February 21st 06 02:11 PM
convert 1.2 miles to meters crlathem Excel Discussion (Misc queries) 4 August 4th 05 10:47 AM


All times are GMT +1. The time now is 05:49 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement