View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ron Rosenfeld Ron Rosenfeld is offline
external usenet poster
 
Posts: 5,651
Default Rounding hh:mm to nearest 15, 30, 45 minutes

On Fri, 15 Jan 2010 17:31:19 -0800, TeriS
wrote:

I, too, am setting up a timesheet. It is a very simple timesheet but I can't
seem to figure out this (what should be) simple function. My question is:

Time In Time Out Total Minutes worked (These are usually 45 min
classes)
9:00 AM 9:45 AM 0:45
9:30 AM 10:15 AM 0:45
9:00 AM 9:35 AM 0:35
Total time worked 1 hr. 20 min. This is the time I need rounded
to the nearest 1/4 hour. I can do it in the same cell or a cell directly
below this one. Whichever is easiest (I am NOT Excel proficient).

TIA,
Teri


I don't understand how you got 1 hr 20 min from those three working times. But,
in general:

=ROUND(YourSumFormula/TIME(0,15,0),0)*TIME(0,15,0)

or, if you have Excel 2007+ or the Analysis Tool Pak installed:

=MROUND(YourSumFormula,TIME(0,15,0))

--ron