Displaying Time In Decimal Formats

  • Thread starter Thread starter Jeremy.Whitney
  • Start date Start date
J

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
 
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.
Click to expand...

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
 
in b6, use your formula
=IF(C5-B5>0.166666666666667;C5-B5-0.0208333333333333;C5-B5)*24, then use
number format

"Jeremy.Whitney"
 
That worked great, thank you very much. Can't believe it was so simple
and I didn't try it. :)
 
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
 
Back
Top