+ Reply to Thread
Results 1 to 4 of 4

Displaying Time In Decimal Formats

  1. #1
    Registered User
    Join Date
    06-15-2006
    Posts
    6

    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. #2
    Ron Rosenfeld
    Guest

    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. #3
    bobocat
    Guest

    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> glsD: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. #4
    Registered User
    Join Date
    06-15-2006
    Posts
    6
    That worked great, thank you very much. Can't believe it was so simple and I didn't try it.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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