Results 1 to 2 of 2

Thread: help with calculate duration & round to nearest 1/4 hour

  1. #1

    help with calculate duration & round to nearest 1/4 hour



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I'm trying to create one formula that calculates the duration between two fields and then round it to the nearest 1/4 hour.

    Right now I'm using two separate formulas in two separate columns and I want to consolodate my steps. For example:
    I'm calculating the duration between 2011-06-17 06:26:28 and 2011-06-17 06:46:28 using =($L2-$J2)
    then rounding it using =ROUND($k2-$m2)*96,0)/96 then rounding to the nearest 1/4 hour using =ROUND($M2*96,0)/96

    There has got to be a more efficient way to consolodate the calculation into one cell but I'm struggling. Any thoughts?

    Lesley

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,090
    Articles
    79
    Blog Entries
    14
    Hi there, and welcome to the forum!

    I'd go with this: =MROUND((L2-J2)*24,0.25)

    If you enter that formula and get a #NAME? error though, it means you don't have the analaysis toolpack activated. To do this in Excel 2003 and earlier, you need to go to Tools-->Addins and check the box next to Analaysis Toolpack. It should be fine in Excel 2007+

    Be aware though, that if you email this file to someone, they should also have the analysis toolpack activated, or they'll get the same error. It's not a big deal, just follow the same steps and they'll be find. The analysis toolpack has shipped with Excel for years, but even with a full install it's never been activated by default (until Excel 2007).

    Hope this helps,
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Posting Permissions

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