# Displaying Time In Decimal Formats

1. ## Displaying Time In Decimal Formats

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.

2. ## Re: Displaying Time In Decimal Formats

On Thu, 15 Jun 2006 21:50:51 -0500, Jeremy.Whitney
<Jeremy.Whitney.29h55n_1150426501.5696@excelforum-nospam.com> wrote:

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

3. ## Re: Displaying Time In Decimal Formats

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
> (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.
>
>
> --
> Jeremy.Whitney
> ------------------------------------------------------------------------
> Jeremy.Whitney's Profile:
> http://www.excelforum.com/member.php...o&userid=35479
> View this thread: http://www.excelforum.com/showthread...hreadid=552538
>

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

#### Thread Information

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1