Getting an answer in hours and tenths of hours

[Woodmangler]

Hi

I have read and re-read so many posts on this - I am just not getting the answer I need - probably my reading and comprehension skills

OK...

Cell C3 has "Start time" in military time cell is formatted Special "Codigo Postal" - someone somewhere said to choose "Special" to get the military time to show all 4 characters, i.e. "0800" not being automatically converted to "800"

Cell D3 has "End time" in military time - 1630 - everything else is the same as C3

Cell E3 has the formula =SUM(D3-C3)/100 -E3 and is formatted Number - 2 decimal places

The answer in E3 is 8.30 - I need it to read 8.5

I am lost....

 

[Woodmangler]

Simply format as Custom 0000.

(That might be same as Special "Codigo Postal". I don't know. I don't have that Special option. Custom 0000 is universally accepted.)




Choose a different example to demonstrate the error in these suggestions. Try 0830 in C3 and 1600 in D3.

Both formulas return about 8.167. The correct answer is 7.5, which is returned by:

=24*(TEXT(D3,"00\:00")-TEXT(C3,"00\:00"))

We cannot subtract D3-C3 directly because that treats the righthand 2 digits as parts of 100 instead of parts of 60. So 1600 - 0830 = 0770 instead of 0730.

PS.... The suggestions to format C3 and D3 as Custom hh:mm are incorrect because you are entering time without the colon, in the first place. Consequently, 0830 and 1600 are interpreted as 830 and 1600 days after 12/31/1899; that is, the dates 4/9/1902 and 5/18/1904.

I changed the cell format to Custom 0000, and the program changed it to "Codigo Postal" - and it appears to be happy with that, so...

The formula =INT((D3-C3)/100)+MOD((D3-C3)/100,1)*100/60 along with the format "Codigo Postal" on the cells works!

Thank you everyone... I was going to ask about having it round off the sum number but I have already hurt my brain

 

[Rick Rothstein]

Choose a different example to demonstrate the error in these suggestions. Try 0830 in C3 and 1600 in D3.

Both formulas return about 8.167. The correct answer is 7.5, which is returned by:

=24*(TEXT(D3,"00\:00")-TEXT(C3,"00\:00"))

We cannot subtract D3-C3 directly because that treats the righthand 2 digits as parts of 100 instead of parts of 60. So 1600 - 0830 = 0770 instead of 0730.

Good point!

 

[joeu2004]

Choose a different example to demonstrate the error in these suggestions. Try 0830 in C3 and 1600 in D3. Both formulas return about 8.167. The correct answer is 7.5, which is returned by:

=24*(TEXT(D3,"00\:00")-TEXT(C3,"00\:00"))

The formula =INT((D3-C3)/100)+MOD((D3-C3)/100,1)*100/60 [....] works!

No, it does not! As I demonstrated. Oh well, "you can lead a horse to water, but ...".

FYI, in case your military times are less than 24 hours apart, but with midnight in between, use:

=24*MOD(TEXT(D3,"00\:00")-TEXT(C3,"00\:00"),1)

That formula also works when midnight is entered as 2400 into C3.

And to answer your unasked question.... To round to the nearest multiple of 15 minutes, for example:

=MROUND(1440*MOD(TEXT(D3,"00\:00")-TEXT(C3,"00\:00"),1),15)/60

Replace 15 with any other factor of 60 (e.g. 10).

-----
PS.... I just realized that your subject line says "tenths of hours". To round the actual value (not just its appearance) to tenths of an hour, use:

=ROUND(24*MOD(TEXT(D3,"00\:00")-TEXT(C3,"00\:00"),1),1)

Of course, to ensure that just the appearance is rounded, it is sufficient just to format as Number with 1 decimal place.