Displaying Time In Decimal Formats

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jeremy.Whitney - 16 Jun 2006 03:50 GMT

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

--
Jeremy.Whitne

Reply to this Message

Ron Rosenfeld - 16 Jun 2006 04:31 GMT

>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
[quoted text clipped - 13 lines]
>(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

Reply to this Message

bobocat - 16 Jun 2006 04:35 GMT

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.29h55n_1150426501.5696@excelforum-nospam.com> ¼¶¼g©ó¶l¥ó·s»D:Jeremy.Whitney.29h55n_1150426501.5696@excelforum-nospam.com...

> 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
[quoted text clipped - 13 lines]
> (including this one). Any help you could offer would be greatly
> appreciated.

Reply to this Message

Jeremy.Whitney - 16 Jun 2006 09:42 GMT

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

Signature

Jeremy.Whitney

Reply to this Message

Dan Dreger - 17 Sep 2006 06:45 GMT

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

Reply to this Message