Displaying Time In Decimal Formats

[Jeremy.Whitney]

I created a spreadsheet for making schedules, and am attempting to forc
time to display in hours (as in 7.5 hours), instead of normal tim
(7:30). I can't find a decent way of displaying it properly. Here's ho
I have it set up currently:

B5: Start_Time
C5: End_Time
B6: =IF(C5-B5>0.166666666666667;C5-B5-0.0208333333333333;C5-B5)

The formula in B6 just checks to see if the scheduled shift is MOR
than 4 hours long, and if so, subtracts half an hour from the shif
(for a lunch break), and then displays the result. I would like th
result to appear in hour format, as in 7.5, instead of time format
like it is currently. I have attempted every variation of the numbe
formatting I could think of, as well as searched numerous forum
(including this one). Any help you could offer would be greatl
appreciated

 

[Ron Rosenfeld]

I created a spreadsheet for making schedules, and am attempting to force
time to display in hours (as in 7.5 hours), instead of normal time
(7:30). I can't find a decent way of displaying it properly. Here's how
I have it set up currently:

B5: Start_Time
C5: End_Time
B6: =IF(C5-B5>0.166666666666667;C5-B5-0.0208333333333333;C5-B5)

The formula in B6 just checks to see if the scheduled shift is MORE
than 4 hours long, and if so, subtracts half an hour from the shift
(for a lunch break), and then displays the result. I would like the
result to appear in hour format, as in 7.5, instead of time format,
like it is currently. I have attempted every variation of the number
formatting I could think of, as well as searched numerous forums
(including this one). Any help you could offer would be greatly
appreciated.

As far as I know, you cannot do that with formatting.

If you want to display time as decimal hours, you will need to convert the time
value to hours with a formula.

=time_value * 24

Then format the result as something like 0.00


--ron

 

[bobocat]

in b6, use your formula
=IF(C5-B5>0.166666666666667;C5-B5-0.0208333333333333;C5-B5)*24, then use
number format

"Jeremy.Whitney"

 

[Jeremy.Whitney]

That worked great, thank you very much. Can't believe it was so simple
and I didn't try it.

 

[Dan Dreger]

Format cell B6 as General and try this:

=IF((C5-B5)*24>4,(C5-B5)*24-0.5,(C5-B5)*24)

4 hours displays 4
5 hours displays 4.5 (5 - 0.5)

Hope this helped!

Dan