+ Reply to Thread
Results 1 to 5 of 5

Round to nearest 15 then nearest hour if less than 1 hour

  1. #1
    Registered User
    Join Date
    04-02-2014
    Location
    maryville, tn
    MS-Off Ver
    Excel 2010
    Posts
    2

    Round to nearest 15 then nearest hour if less than 1 hour

    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 formTimesheet Form 2014.xlsx

  2. #2
    Valued Forum Contributor
    Join Date
    09-25-2011
    Location
    Eastbourne, England
    MS-Off Ver
    Excel 2010
    Posts
    1,089

    Re: Round to nearest 15 then nearest hour if less than 1 hour

    Hi BLOUNTFIRE

    Welcome to the forum. If you put the following formula in column M (format as hh:mm), it will do as you ask

    Please Login or Register  to view this content.
    To explain:
    1 - the "(L18<I18)*1" means add 1 (day) if the end time is less than the start time ( (L18<I18) is Boolean, so either 1 if true or 0, so 1*1 or 0*1)
    2 - Max(xxx,yyy) (self explanatory) 1 = day hence 1/24 = hour, 1/24 = .041667

    Let me know if you have any questions

    Regards
    Alastair

  3. #3
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Round to nearest 15 then nearest hour if less than 1 hour

    Doesn't the result need to be a decimal number of hours? Try this to cope with crossing midnight, to return decimal hours, with a minimum of 1

    =MAX(1,MOD(L18-J18,1)*24)
    Audere est facere

  4. #4
    Registered User
    Join Date
    04-02-2014
    Location
    maryville, tn
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Round to nearest 15 then nearest hour if less than 1 hour

    =MAX(1,MOD(K24-I24,1)*24)

    using this formula which works great. Thank You.
    Now I have to have the total to show up at the bottom. It only shows #VALUE! How do I make #VALUE! show as a 0?

  5. #5
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,782

    Re: Round to nearest 15 then nearest hour if less than 1 hour

    how about this one:
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Ben Van Johnson

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Enter Current Time then Round to Nearest Quarter Hour
    By tbstein99 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-26-2014, 05:07 PM
  2. [SOLVED] To round a sum of Nums to nearest quarter of an hour from numbers that are themselves sums
    By Webmastereol in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-06-2013, 03:01 PM
  3. where to put formula to round hoursbto nearest quarter hour
    By MAINTENANCEMANCINCY in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-27-2012, 03:36 AM
  4. How do I round time to the nearest quarter of an hour
    By Meghan in forum Excel - New Users/Basics
    Replies: 6
    Last Post: 07-07-2006, 01:40 PM
  5. [SOLVED] Need to round the time to the nearest quarter hour. Help
    By John in forum Excel General
    Replies: 1
    Last Post: 02-11-2006, 02:45 AM

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