Results 1 to 3 of 3
  1. #1
    5 Star Lounger kmurdock's Avatar
    Join Date
    Feb 2003
    Location
    Pacific Grove, California, USA
    Posts
    716
    Thanks
    10
    Thanked 34 Times in 28 Posts

    Turning time into a decimal (2003 SP2)

    Hey all,

    I'm a Word person and never thought I'd be posting here. However, trooper that I am, I've been working on a fairly sophisticated spreadsheet for time entry and need some formula help!

    The spreadsheet takes time and turns it into a number with a decimal. So seven and half hours is displayed 7.5 rather than 7:30. If one is figuring time to the nearest 1/10th of an hour, this works nicely -- constrain the cell to one decimal point and it rounds up or down all by itself.

    However, the company I'm working for wants to measure in 15-minute increments, rounding up or down as appropriate. So, for example, 7:40, 7:45 and 7:50 would all be displayed as 7.75.

    How can I take a decimal such as 7.55 and push it down to 7.5, while pushing 7.71 up to 7.75?

    Thanks much in advance! <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35>

    Kim

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Turning time into a decimal (2003 SP2)

    Say you have a tim (in decimal hours) in A1. You can use the formula

    =ROUND(4*A1,0)/4

    to round to the nearest multiple of 0.25 = 1/4.

    If you've got the Analysis ToolPak add-in installed, you can also use

    =MROUND(A1,0.25)

    but that would require *all* users to have the Analysis ToolPak installed.

  3. #3
    5 Star Lounger kmurdock's Avatar
    Join Date
    Feb 2003
    Location
    Pacific Grove, California, USA
    Posts
    716
    Thanks
    10
    Thanked 34 Times in 28 Posts

    Re: Turning time into a decimal (2003 SP2)

    Hi Hans,

    Yes, they both worked perfectly. Exactly what I need.

    I'll use the ROUND function so they don't have to load the Analysis Toolpak on all their workstations.

    Thank you thank you thank you! <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>

    K

Posting Permissions

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