Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old January 16th 10, 01:31 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2010
Posts: 1
Default 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   Report Post  
Old January 16th 10, 01:46 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 15,768
Default 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   Report Post  
Old January 16th 10, 01:56 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
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
  #4   Report Post  
Old January 16th 10, 02:45 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2009
Posts: 277
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



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   Report Post  
Old January 16th 10, 02:47 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2009
Posts: 36
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



Check out the time sheet he

http://office.microsoft.com/en-us/te...CT101172771033

Look at Wally's other workbooks too.


  #6   Report Post  
Old July 9th 18, 04:57 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2018
Posts: 1
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Rounding up or down to the nearest $.09 Sandy G. Excel Discussion (Misc queries) 3 July 31st 08 03:51 PM
Rounding to nearest 9 GaryS Excel Worksheet Functions 20 June 6th 08 06:15 PM
rounding to nearest 100 rdwngr23 Excel Worksheet Functions 2 December 17th 07 11:21 PM
I need a formula with rounding up & rounding down to the nearest . Tony Kay Excel Worksheet Functions 3 May 29th 07 11:13 PM
Rounding times to the nearest 15 minutes in Excel BuckeyeWMV Excel Discussion (Misc queries) 8 April 3rd 07 10:28 PM


All times are GMT +1. The time now is 01:53 AM.

Powered by vBulletin® Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
Copyright 2004-2018 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017