+ Reply to Thread
Results 1 to 6 of 6

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

  1. #1
    Registered User
    Join Date
    05-02-2013
    Location
    PA, USA
    MS-Off Ver
    Excel 2007
    Posts
    6

    Question 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!
    Attached Files Attached Files

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX & Windows
    Posts
    7,326

    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
    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

  3. #3
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX & Windows
    Posts
    7,326

    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. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,123

    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
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  5. #5
    Registered User
    Join Date
    05-02-2013
    Location
    PA, USA
    MS-Off Ver
    Excel 2007
    Posts
    6

    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. #6
    Registered User
    Join Date
    05-02-2013
    Location
    PA, USA
    MS-Off Ver
    Excel 2007
    Posts
    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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