Round To Nearest 15 Then Nearest Hour If Less Than 1 Hour

Apr 4, 2014

I have a form to round to nearest quarter but if it is less than 1 hour I need it to round to a total of 1. Can this be combined in one formula.

I also need my time to be configured so that if the start time is a PM number then end time AM it does not figure right. is there a way to remove the AM/PM from time. I have already tried all the formats from number,time, & custom.

Attached is my form : Timesheet Form 2014.xlsx

View 4 Replies


ADVERTISEMENT

Timesheet - Round DOWN To Nearest 1 / 10 Hour

Jan 2, 2013

I am trying to figure out a formula that will provide the following results for time worked. (This is contract work that is billed in 1/10 hr increments.)

1 min - 5 min = no charge (rounddown to 0 min)
6 min - 11 min - rounddown to 6 min
12 min - 17 min - rounddown to 12 min.

or

1 - 5 min are billed at 0 hours
6 - 11 min are billed at .1 hour
12 - 17 min are billed at .2 hours

B15 = start time, C15 = end time, G15 = billable time in 1/10 hrs

View 4 Replies View Related

How Do I Round To Nearest Quarter Hour

Sep 23, 2003

I need to round a time to the nearest quarter hour and have it show in quarters. My formula figures out the hours worked. If the total is 8.36, then it needs to show 8.75. It needs to round up or down to nearest quater hour.

View 9 Replies View Related

Enter Current Time Then Round To Nearest Quarter Hour?

Mar 26, 2014

I work at a facility where we care for adults with disabilities and we need to record times that clients arrive and leave. I created an attendance check-in sheet that needs the current time entered quickly. Is there code that will allow staff to double click on a cell and have the current time entered and rounded to the nearest quarter hour?

View 1 Replies View Related

Round Up A 24HR Format Time To The Nearest Quarter Of An Hour

Feb 5, 2009

Is it possible to round up or down a 24HR time format.

ie. 18:06 would be 18:00
18:28 would be 18:30?

View 9 Replies View Related

Round To The Nearest 5

Feb 20, 2009

I am using the rounding function in excel and its rounding to the 10's, is there a way to round to the nearest 5?

View 4 Replies View Related

Round To Nearest .05 Or .09

Feb 6, 2010

Below is my current formula. Right now I have it rounding the results to the nearest .09. I would also like to have it round to the nearest .05 as well.

In other words, to be more exact: I need all numbers that end in 0 or 1 to be rounded down to 9; any numbers that end in 7 or 8 to be rounded up to 9; any numbers that end in 2, 3, or 4 to be rounded up to 5; and 6 to be rounded down to 5. And, of course any numbers ending in 5 or 9 shouldn't change.

View 14 Replies View Related

Round Up To Nearest Whole 50?

May 23, 2012

I am using the MROUND function and it will round up or down to the nearest whole 50. Is there a function to round only UP to the nearest whole 50?

ie: 1704 is being rounded to 1700 I need 1704 to rounded to 1750

View 3 Replies View Related

Round Up To Nearest X`

Jun 9, 2007

I trying to make a formula that will multiply a cell by 1.43 and then round the total up to the nearest 45 or 95 with no decimal points.

View 9 Replies View Related

Round To Nearest 0.99 If Value Is Greater Than 0.95 Or Less Than 0.05

Dec 12, 2013

I am looking for a formula which will allow a value to round up or down to the nearest x.99 if the value is greater than x.95 or less x.05 otherwise remain the same. i.e if the value is 0.96 it should read 0.99 and if the value is 1.04 it should read 0.99. however if the value 1.05 it should read 1.05.

View 9 Replies View Related

Round Number To Nearest .05

Feb 18, 2013

I need to round numbers up in a spreadsheet to the nearest .05 . For example, I would need to change 1.502 to 1.55 or 1.556 to 1.60 .

View 3 Replies View Related

Round Function Nearest .5

Oct 30, 2009

I'm trying to round some figures UP to the nearest .5, However i only know this formula: =MROUND(P2,0.5) and that rounds to the nearest .5? Is there anything i can use instead to make sure it is always up not down. so 6.13 becomes 6.50 or 7.53 become 8.00? etc.

View 2 Replies View Related

Round To The Nearest Work Day

Dec 3, 2007

How do I format the date to be to the nearest work day?

I want to establish a due date for an asset that is due 5 days before launch. If my launch date is in A1, and my due date is in A2, in A2 I put:

=A1-5

So how do I make sure that the resulting date is a workday?

View 9 Replies View Related

Round Time Value Like 08:23:51 To Nearest 15 Minute Like 08:30:00?

Jun 21, 2012

How could round a time value like 08:23:51 to the nearest 15 minute like 08:30:00?

View 2 Replies View Related

Use Single Function To Round To Nearest 10?

Dec 18, 2012

I want to use a single function to round to nearest 10 e.g. 1036 should round to 1040 and 1032 should round to 1030

View 3 Replies View Related

Excel 2010 :: Round To Nearest 0.25?

Feb 26, 2013

I'm using Excel2010. I have a spreadsheet of loan information contained in rows. One of the columns lists the loan rate as a number (not a percent - so 5.25% is listed as 5.25, not 0.0525 and formated as percent). I need to pivot-table using the rate, rounded up to the nearest quarter of a percent (so 5.3875 rounds up to 5.50). Is there a formula to do this, or is it better to use a lookup table?

View 4 Replies View Related

Round To Nearest WHOLE NUMBER That Is Multiple Of 5?

Feb 17, 2014

I have numbers ranging from 1 to 90 that need to be rounded the nearest whole number that is divisible by 5.

Expected outcome

1 should round up to five88 should round up to 9072 should round down to 70

Round - modified for multiples of five* - works perfectly well for number that are greater than or equal to 3. However, 1 and 2 round to zero.

I have tried using IF to have the formula ROUNDUP for 1 and 2 and ROUND for 3 and up with no luck.

Every result I have seen with the Google says the solution is "=ROUND(A1*0.05,0)". This does not work.

VBA is an option for this workbook.

*ROUND(A1/5,0)*5))

View 5 Replies View Related

Round Time To Nearest 15 Minutes

Oct 6, 2008

I need a formula to take an entered tim and rounded it to the nearest 1/4 hour

View 9 Replies View Related

Round Up To Nearest Number Within A List

Mar 23, 2009

I guess I have an odd rounding up query. I want to know if its possible to make excel round up a number to the nearest number from within a list of numbers.

For instance my list could be

10
12
15
17
19
22
25
30
35
42

The numbers arent odds or evens or multiples of anything so I'm guessing there won't be an inbuilt function to do this but is there anyway I could get to the answer in a more round about way with helper calculations??

I guess I could use the following -

=IF(A1>42,"Error",IF(A110,A112,A1

View 9 Replies View Related

Round To The Nearest Number In Te Table

Nov 5, 2009

I Need Formula To Number To The Nearest Number In Te Tabel

View 9 Replies View Related

Round Up To Nearest Significant Multiple

Apr 3, 2008

This issue is due to the limitations of the nested if formula. I have range of cells that contains percentile rankings 1-100. I am trying to figure out how to convert the cells that contain values 1-10 to a 1, cell values 11-20 to a 2 and so on.

View 2 Replies View Related

Round In Pivot Table To Nearest X

Jun 6, 2008

In trying to use MROUND to round time to the nearest .25 I am getting an error " Reference names and arrays are not supported in Pivot Table Fromulas.

Here is my formula for the calculated field =MROUND( 'Numeric duration',.25) Whereas Numeric duration is the name of the Pivot table field.

View 3 Replies View Related

Round Up The Sum Of A Column Figures To The Next Nearest Quarter

Apr 2, 2009

I need to round up the sum of a column figures to the next nearest quarter. For example: $1.11 would be rounded to $1.25, or say $1.83 would be rounded to $2.00. What formula would I need to do this? I'm using Excel 2000

View 2 Replies View Related

Can Make A Cell Round Value To Nearest Whole Number?

Dec 10, 2012

I have a cell that is going to be used for the quantity of a certain item. I would like to dummy proof this sheet as much as possible so that if someone puts in 21.5, that it will do something like change the value to either 21 or 22, since there can't really be a .5 of this item.

View 5 Replies View Related

To 'round' A Time To The Next Nearest 15min Interval

Jun 23, 2006

Is it possible to 'round' a time to the next nearest 15min interval?

As an example in cell A1 I have a value that returns 2:07 PM (its formated
as h:mm AM/PM), but in B1 I wish to translate this to the nearest 15min
interval in an hour which is 2:15 PM, if the value in A1 was 5:39 PM I would
want to show 5:45 PM in B1 etc etc

View 13 Replies View Related

Multiply Number By 3% And Round To Nearest Dime

Feb 2, 2012

I am doing a huge price increase for a price book and need to rasie each price by 3% and have it rounded to the nearest dime.

Is there a formula to do this in one go and not have one cell with the rounded price and another for the nearest dime price.

View 2 Replies View Related

Calculate Time, But Round To Nearest 15 Minutes

Jan 2, 2007

I am trying to calculate the time between two cells and have the answer in hours, with two decimals, but in .25 minute increments.

Desired result:
1/1/07 12:00 in A1
1/1/07 14:10 in B1
1.25 in C1

0-7 Minutes - round down to 0.00
8-22 Minutes - round to 0.25
23-37 Minutes - round to 0.50
38-52 Minutes - round to 0.75
52-60 Minutes - round to 1.00

Because this is billable time, the number is never negative, so I don't need the formula to verify the first date/time cell is less than the second.

View 9 Replies View Related

Entering Data In Cell And Having It Round To The Nearest .25

Sep 24, 2007

Is it possible to have this function work to round in the same cell the data is entered in? ...

View 9 Replies View Related

Round To Nearest Even Number. Bankers Rounding

May 15, 2008

We are trying to find the correct formula to round number to the nearest even number. In the attached example, 0.105 should round to 0.10 since the zero to the left of the five is even. If the problem was 0.115 then the answer would be 0.12 since the one to the left of the zero is odd.

View 9 Replies View Related

Rounding Formula :: Round To The Nearest Integer (whole Number)

Feb 3, 2007

I would like to round this number (3.5) to become 4, but by using this formula =CEILING(A1,0.5) the result will be 3.5

i need a formula that turns 3.5 to 4

when i use this formula =ROUND(A2,0.5), 3.5 becomes 4, but 3.2 becomes 3 not 3.5

i.e. a formula turns 3.2 to 3.5 and 3.5 to 4

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved