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