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.

Thank you in advance!

if you format the cell as HH:MM
remove your x24
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

just thought

try the same with ceiling

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

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

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.