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

lesleyj

New member
Joined
Jul 8, 2011
Messages
1
Reaction score
0
Points
0
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
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,522
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
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,
 
Top