Difference between two times
 

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 ?

Difference between two times
 

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

Difference between two times
 

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 ?

Difference between two times
 

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

Difference between two times
 

Perfect!

Thanks.

Difference between two times
 

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

Difference between two times
 

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

Difference between two times
 

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.

Difference between two times
 

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.

Difference between two times
 

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.

Difference between two times
 

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.

--

Difference between two times
 

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?

Difference between two times
 

Hi dpq,

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

Difference between two times
 

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.

--

Difference between two times
 

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

Difference between two times
 

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.

--

Difference between two times
 

dpb: That works!

Difference between two times
 

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.

--

Difference between two times
 

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)

Difference between two times
 

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... :)


--

Difference between two times
 

28.32 days

Difference between two times
 

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

Difference between two times
 

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