# To round a sum of Nums to nearest quarter of an hour from numbers that are themselves sums

1. ## To round a sum of Nums to nearest quarter of an hour from numbers that are themselves sums

Hi,

I am working on a time sheet for my employer. I have the time sheet set up so that "hours worked" are automatically calculated using the SUM function ( the function I am using is set up as: SUM((a2-a1)+(a4-a3)+(a6-a5))*24; where a2,a4,a6 are sign out times and a1,a3, a5 are sign in times; where column a represents Monday, column b represents Tuesday, and so on). This part works.

Now, I am trying to add together the sums for each day and have the total be rounded to the nearest quarter of an hour (such as making 18.3 into 18.25). I can get this to happen with numbers that I plug in myself but not using the sum of numbers that were themselves summed by a formula.

I have tried googling several "versions" of the SUM and ROUND functions, none of which worked, including SUM(ROUND) and ROUND(SUM) with and without making an array.

I have attached my excel file.

2. ## Re: To round a sum of Nums to nearest quarter of an hour from numbers that are themselves

if you format the cell as HH:MM
then you can use mround( your calc , "0:15" )

=MROUND((SUM((B27-B26)+(B29-B28)+(B31-B30))),"0:15")

but that will round down as well as up to nearest 15mins

i guess you always want to round up

3. ## Re: To round a sum of Nums to nearest quarter of an hour from numbers that are themselves

just thought

try the same with ceiling

=CEILING((SUM((B27-B26)+(B29-B28)+(B31-B30))),(15/(60*24)))

4. ## Re: To round a sum of Nums to nearest quarter of an hour from numbers that are themselves

or maybe this...
=MROUND(SUM(B14:H14),0.25)

0.25 = 1/4 hour = 15 mins

5. ## Re: To round a sum of Nums to nearest quarter of an hour from numbers that are themselves

Thank you etaf and FDibbins. I will give those suggestions a try and let you know how it goes!

6. ## Re: To round a sum of Nums to nearest quarter of an hour from numbers that are themselves

There must have been something wrong with my worksheet in the file. The formulas, any of them, worked fine except in the particular cell I actually wanted it in. I am now creating a new file and starting over again. Thank you both for your help. Once I verify that I can get the new file working perfectly, I'll close the thread.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1