Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rounding hh:mm to nearest 15, 30, 45 minutes
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rounding hh:mm to nearest 15, 30, 45 minutes
Total time worked...1 hr. 20 min
I think your total time should be 2:05. Rounded to the nearest quarter hour would be 2:00. Try this... =ROUND(SUM(C2:C4)*96,0)/96 -- Biff Microsoft Excel MVP "TeriS" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 If you want to tally only 15 minute increments, you can (should) only use 15 minute increments. The "9:35" entry is invalid in such a policy. If you FORCE all entries to be at 15 minute increments only, you no longer need to round anything off. You can do that by way of "data validation". |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 Check out the time sheet he http://office.microsoft.com/en-us/te...CT101172771033 Look at Wally's other workbooks too. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rounding hh:mm to nearest 15, 30, 45 minutes
On Friday, January 15, 2010 at 7:31:19 PM UTC-6, 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 You can put this below/to the right (or in any empty cell really) of the value you want rounded to the nearest 15 minute mark and replace the "A1" cell reference in the formula with a reference to the cell you are wanting to round. =mround(A1, 1/96) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Rounding up or down to the nearest $.09 | Excel Discussion (Misc queries) | |||
Rounding to nearest 9 | Excel Worksheet Functions | |||
rounding to nearest 100 | Excel Worksheet Functions | |||
I need a formula with rounding up & rounding down to the nearest . | Excel Worksheet Functions | |||
Rounding times to the nearest 15 minutes in Excel | Excel Discussion (Misc queries) |