F16 contains 18:30:00 and is displayed as 6:30 PM
H16 contains 20:42:00 and is displayed as 8:42 PM

(Both cells a Format Cells: Number; Custom; Type: h:mm AM/PM)

What formula do I put in D16 so the difference between H16 and F16 is displayed as 2 hr 12 mins ?

Hi,

Am Fri, 27 Apr 2018 10:46:18 -0700 (PDT) schrieb GARYWC:

F16 contains 18:30:00 and is displayed as 6:30 PM
H16 contains 20:42:00 and is displayed as 8:42 PM

(Both cells a Format Cells: Number; Custom; Type: h:mm AM/PM)

What formula do I put in D16 so the difference between H16 and F16 is displayed as 2 hr 12 mins ?

try:
=INT((H16-F16)*24)& " hrs "&ROUND(MOD((H16-F16)*24,1)*60,0)&" mins"


Regards
Claus B.
--
Windows10
Office 2016

Perfect!

Now, B16 contains 6.73 (Format Cells: Category: Number; Decimal Place = 2) (This is the distance (i.e., miles).

What formula will compute the miles-per-hour (MPH) of B16 / D16 ?

Hi,

Am Fri, 27 Apr 2018 11:40:46 -0700 (PDT) schrieb GARYWC:

Now, B16 contains 6.73 (Format Cells: Category: Number; Decimal Place = 2) (This is the distance (i.e., miles).

What formula will compute the miles-per-hour (MPH) of B16 / D16 ?

try:
=B16/((H16-F16)*24)


Regards
Claus B.
--
Windows10
Office 2016

Perfect!

Thanks.

Hi again,

try:
=INT((H16-F16)*24)& " hrs "&ROUND(MOD((H16-F16)*24,1)*60,0)&" mins"

you can also try:
=TEXT(H16-F16,"h")&" hrs "&TEXT(H16-F16,"m")&" mins"


Regards
Claus B.
--
Windows10
Office 2016

Your last formula always results in 2 hrs 1 mins regardless of the values in H16 and F16.

Your =TEXT(H16-F16,"h")&" hrs "&TEXT(H16-F16,"m")&" mins" formula results in 2 hrs 1 mins, regardless of the values in H16 and F16.

The times are in E16 and G16 so I've changed Claus's formula to:

=TEXT(G16-E16,"h")&" hrs "&TEXT(G16-E16,"m")&" mins" formula.

When I paste that formula in C16, the result is 2 hrs 1 mins.

When I paste that formula into the other cells in column C, the result is 2 hrs 1 mins, regardless of the contents in columns E and G.

On Monday, 30 April 2018 04:12:35 UTC+12, GARYWC wrote:
The times are in E16 and G16 so I've changed Claus's formula to:

=TEXT(G16-E16,"h")&" hrs "&TEXT(G16-E16,"m")&" mins" formula.

When I paste that formula in C16, the result is 2 hrs 1 mins.

When I paste that formula into the other cells in column C, the result is 2 hrs 1 mins, regardless of the contents in columns E and G.

Don't paste the formula text, copy and paste the whole cell itself, to the other cells in column C, then Excel will automatically adjust the row numbers.

On 4/29/2018 11:12 AM, GARYWC wrote:
The times are in E16 and G16 so I've changed Claus's formula to:

=TEXT(G16-E16,"h")&" hrs "&TEXT(G16-E16,"m")&" mins" formula.

When I paste that formula in C16, the result is 2 hrs 1 mins.

When I paste that formula into the other cells in column C, the result is 2 hrs 1 mins, regardless of the contents in columns E and G.

That doesn't work because the "m" time specifier isn't smart enough to
parse off the hours and only work on the minutes left after the hours
are occupied.

The following works here; had to use TEXT() for the minutes to avoid
rounding displaying something like 2 hr 11.999999999 min

=INT((H16-F16)*24) &" hr" & " " &
TEXT(MOD((H16-F16)*24,INT((H16-F16)*24))*60,"#") &" min"

It's the typical Excel gibberish since can't have any temporary
variables or the like to write something legible in cell formulae.

--

Hi Simon,

I copied the formula and pasted it into C16. C16 contains =TEXT(G16-E16,"h")&" hrs "&TEXT(G16-E16,"m")&" mins". The result is displayed as 2 hr 1 mins.

I then copy cell C16 and paste it into the other cells in column C. Each cell contains the formula (with the adjusted row numbers). But the result in each cell is displayed as 2 hr 1 mins (even though the contents of the cells in columns G and E are different).

What am I doing wrong?

Hi dpq,

Your formula works in all of the cells except for two (their results are #DIV/0!)

On 4/29/2018 3:07 PM, GARYWC wrote:
Hi dpq,

Your formula works in all of the cells except for two (their results are #DIV/0!

Hard to work w/o data; I didn't save the sample worksheet but there's
good likelihood you'll have to have one or more IF() clause(s) to handle
<1hr intervals and maybe 0 minutes.

All and all, it'd be a much easier job for a VBA function instead of
trying to do it in cell formula...altho I've not looked at all the
DATE/TIME/TEXT functions, perhaps there are some that will parse off the
hour/minute sections rather than having to compute them...internally the
time differential is in days/fractions of day as integer value. Hence
the 2:12 number you have for the one example is actually

2/24+12/(24*60)
ans =
0.0916666666666667


which you'll see if you turn the cell display to 'general' instead of time.

So, you have to reverse that operation to turn 0.091666... back into 2
and 12 and then you get floating point rounding issues plus the problems
of what happens if mod() or whoever does have a zero.

--

I don't know how to provide you with my actual spreadsheet so you can see the data

On 4/29/2018 3:34 PM, dpb wrote:
On 4/29/2018 3:07 PM, GARYWC wrote:
Hi dpq,

Your formula works in all of the cells except for two (their results
are #DIV/0!

Hard to work w/o data; I didn't save the sample worksheet but there's
good likelihood you'll have to have one or more IF() clause(s) to handle
<1hr intervals and maybe 0 minutes.

All and all, it'd be a much easier job for a VBA function instead of
trying to do it in cell formula...altho I've not looked at all the
DATE/TIME/TEXT functions, perhaps there are some that will parse off the
hour/minute sections rather than having to compute them..

....

And, there are...

=HOUR(I1-H1) & " hr " & MINUTE(I1-H1) & " min"

is ok as long as you're happy of it shows up as

0 hr 0 min

for either portion being zero; otherwise you're back to IF() clauses.

--

dpb: That works!

On 4/29/2018 4:35 PM, GARYWC wrote:
dpb: That works!

Yeah, shoulda' thunk a little more first instead of trying to just fixup
the other by converting instead of using builtin methods; at least there
were some for this case, often there aren't or they're rudimentary at best.

--

My spreadsheet contains data related to my walking excursions.

For each day I walk, I record (for example):

Date Friday, April 27
Miles 7.57
Duration 2 hrs 27 mins
MPH 3.09
Start time 5:40 PM
Start temp 72
End time 8:07 PM
End temp 61
Route Monroe, La Sierra, McAllister Pkwy, Dufferin, Monroe, Tropicana

It also shows the number of walking days, the total number of miles and the straight-line distance from my home to various cities. For example:

In 149 walking days, I've walked 967.81 straight-line miles from Riverside CA to:

10 miles S of Seattle, Washington
64 miles SW of Rapid City, South Dakota
184 miles NW of San Antonio, Texas
132 miles NW of Kerrville, TX (in the path of April 2024 total solar eclipse)

On 4/29/2018 6:30 PM, GARYWC wrote:
....

In 149 walking days, I've walked 967.81 straight-line miles from Riverside CA to:

10 miles S of Seattle, Washington
64 miles SW of Rapid City, South Dakota
184 miles NW of San Antonio, Texas
132 miles NW of Kerrville, TX (in the path of April 2024 total solar eclipse)

Head towards Wichita and at that rate in another 30 days you'd be about
where I am... :)


--

28.32 days

I was in Roberts, Idaho for the 2017 total solar eclipse.

If we're still alive in April 2024, we're going to Kerrville, Texas to see the total solar eclipse again.