Getting an answer in hours and tenths of hours

Woodmangler · Dec 22, 2015

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

Rick Rothstein · Dec 22, 2015

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

Your formula subtracts the value in E3 ?

But the formula is in E3 ???

Please clarify.

Roger Govier · Dec 22, 2015

Hi

You need to format your cells as Number > Custom > hh:mm
That is the correct format for time
Then E3=(D3-C3) would yield a result of 0.35 (assuming you had formatted this cell as Number, 2 dec places
That is because Excel stores times as fractions of a day (24 hours) so
E3=(D3-C3)*24 will yield your result of 8.5

JoeMo · Dec 22, 2015

Woodmangler said:
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....

Is this what you want??

Excel Workbook

C

D

E

3

0800

1630

8.50

Sheet14

cb366374 · Dec 22, 2015

If the times in C3 and D3 (0800, 1630) are formatted like this "hh:mm;@", then something like this should work

Code:

=HOUR(D3-C3)+MINUTE(D3-C3)/60

You will want the formatting for this cell to be general or number though because 8.5 is not a time.

Rick Rothstein · Dec 22, 2015

JoeMo said:
Is this what you want??
Sheet14

* C D E
3 0800 1630 8.50

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula
E3 =INT((D3-C3)/100)+MOD((D3-C3)/100,1)*100/60

<tbody>
</tbody>

<tbody>
</tbody>
Excel tables to the web >> Excel Jeanie HTML 4

If so, here is a shorter formula which I think will return the same value as your formula...

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

Woodmangler · Dec 22, 2015

Roger Govier said:
Hi

You need to format your cells as Number > Custom > hh:mm
That is the correct format for time
Then E3=(D3-C3) would yield a result of 0.35 (assuming you had formatted this cell as Number, 2 dec places
That is because Excel stores times as fractions of a day (24 hours) so
E3=(D3-C3)*24 will yield your result of 8.5

"You need to format your cells as Number > Custom > hh:mm"

When I format the cell that way, entering any military time yields "00.00" no matter what I enter

Woodmangler · Dec 22, 2015

Rick Rothstein said:
Your formula subtracts the value in E3 ?

But the formula is in E3 ???

Please clarify.

Nope... I screwed up posting. I was trying to get it to check another cell to see if any lunch time was taken. Sorry

Roger Govier · Dec 22, 2015

Woodmangler said:
"You need to format your cells as Number > Custom > hh:mm"

When I format the cell that way, entering any military time yields "00.00" no matter what I enter

You enter as 8:30 and 16:30 then it will work

joeu2004 · Dec 22, 2015

Woodmangler said:
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

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

JoeMo said:
=INT((D3-C3)/100)+MOD((D3-C3)/100,1)*100/60

Rick Rothstein said:
=24*TEXT(D3-C3,"0\:00")

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.

Getting an answer in hours and tenths of hours

Woodmangler

New Member

Excel Facts

Rick Rothstein

MrExcel MVP

Roger Govier

Active Member

JoeMo

MrExcel MVP

cb366374

Board Regular

Rick Rothstein

MrExcel MVP

Woodmangler

New Member

Woodmangler

New Member

Roger Govier

Active Member

joeu2004

Banned user

Similar threads

Forum statistics

Share this page

Getting an answer in hours and tenths of hours

New Member

Excel Facts

MrExcel MVP

Active Member

MrExcel MVP

Board Regular

MrExcel MVP

New Member

New Member

Active Member

Banned user

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock