Formula Help - Time it takes to run 100 miles and then convert it to time of day

[Hankmardukaas]

Looking for some help or points to create a formula to provide the average time it will take to run 100 miles at 14.4 minutes per mile (24hrs total) and then convert that to time of day for certain mileage points.

I have been playing around with the formulas below, but am having some trouble and can't get it to work.

=100 * 14.4 / 60
=TEXT(TIME(HOUR(T1), MINUTE(T1) + 30 * 60, SECOND(T1)), "hh:mm AM/PM") - [with T1 being the 24 hrs from the formula above]

Let me know if you have any questions or need any other information. Any help is greatly appreciated.

Thanks!!

 

[kevin9999]

Book1
	A	B
1	Mile	Arrival time
2	0	6:00 AM
3	6.1	7:27 AM
4	19.4	10:39 AM
5	31.9	1:39 PM
6	43.4	4:24 PM
7	54.8	7:09 PM
8	63.3	9:11 PM
9	70.4	10:53 PM
10	78.3	12:47 AM
11	85.4	2:29 AM
12	92.8	4:16 AM
13	100	6:00 AM
Sheet3

Cell Formulas
Range	Formula
B3:B13	B3	=(A3*14.4/1440)+$B$2

 

[Hankmardukaas]

Thanks, I think this works and is a lot simpler than what I was trying. I am getting the exact same times when I try to adjust the formula for 25 hours and 28 hour times. Do I want to keep cell B2 the same or should it be changing with the time as it goes down the list?

 

[Hankmardukaas]

This seems to be correct, the time for 28 hours comes out faster though. Am i doing something wrong?

 

[Alex Blakenburg]

The divide by 1440 is a conversion of minutes to a fraction of a day and is a constant. It should be the same value used in all your formulas.
eg =(B5*16.8/1440)+$M$4

Excel a 1 = 1 day = 24 Hrs = 24 × 60 mins = 1440 mins

 

[Hankmardukaas]

Oh, haha, thanks for explaining that. I appreciate the help!

 

[kevin9999]

I am getting the exact same times when I try to adjust the formula for 25 hours and 28 hour times.

If you wanted the ETAs for the 28 hour range, simply increase your constant (14.4 m/m) by 1.125. 28/25 = 1.125. Like this:

Book1
	A	B
1	Mile	Arrival time
2	0	6:00 AM
3	6.1	7:38 AM
4	19.4	11:14 AM
5	31.9	2:36 PM
6	43.4	5:43 PM
7	54.8	8:47 PM
8	63.3	11:05 PM
9	70.4	1:00 AM
10	78.3	3:08 AM
11	85.4	5:03 AM
12	92.8	7:03 AM
13	100	9:00 AM
Sheet1

Cell Formulas
Range	Formula
B3:B13	B3	=(A3*16.2/1440)+$B$2

 

[Hankmardukaas]

If you wanted the ETAs for the 28 hour range, simply increase your constant (14.4 m/m) by 1.125. 28/25 = 1.125. Like this:

Book1
	A	B
1	Mile	Arrival time
2	0	6:00 AM
3	6.1	7:38 AM
4	19.4	11:14 AM
5	31.9	2:36 PM
6	43.4	5:43 PM
7	54.8	8:47 PM
8	63.3	11:05 PM
9	70.4	1:00 AM
10	78.3	3:08 AM
11	85.4	5:03 AM
12	92.8	7:03 AM
13	100	9:00 AM
Sheet1

Cell Formulas
Range	Formula
B3:B13	B3	=(A3*16.2/1440)+$B$2

Thanks! really appreciate the help.