+ Reply to Thread
Results 1 to 5 of 5

Excel Round Up time to the nearest minute from total of 2 cells

  1. #1
    Forum Contributor
    Join Date
    04-03-2012
    Location
    Washington State
    MS-Off Ver
    Excel 365
    Posts
    340

    Exclamation Excel Round Up time to the nearest minute from total of 2 cells

    I have a worksheet that is filled in from a userform and have the date + time filled in to 2 different cells, basically from a start and stop button. The button fills current date and time in the cells. So I have it set like this:

    Cell A1 Start = "04/15/2012 4:15:27"
    Cell A2 Stop = "04/15/2012 5:38:42"
    Cell A3 = A2-A1

    My Cell C3 Formula: =(+A2-A1)*24

    I need to have my formula calculate an even time..... like 1.4 / 1.5 / 1.6 hours etc. But instead when I call this number back to the userform I get this: 1.38749999989523
    I only want it to round up to the nearest tenth of an hour.

    How can I make cell A3 show a rounded UP time to the nearest minute no matter what the second is? I have found some examples but nothing for what I need. I already have the Cell A3 formula set for subtracting the time, so not sure how to add this or what to change.

    Thanks for your help, I am really stuck on this.

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Excel Round Up time to the nearest minute from total of 2 cells

    Nearest 10th of an hour

    =ROUND((A2-A1)*24,1)

    ...or nearest minute....

    =ROUND((A2-A1)*1440,0)/1440

    for the latter you'll probably want to format A3 as h:mm
    Audere est facere

  3. #3
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Excel Round Up time to the nearest minute from total of 2 cells

    To round to the next multiple of 0.1 hours, =CEILING(A2-A1, 1/240)

    To round to the next whole minute, =CEILING(A2-A1, 1/1440)

    You can format the result as [h]:mm
    Entia non sunt multiplicanda sine necessitate

  4. #4
    Forum Contributor
    Join Date
    04-03-2012
    Location
    Washington State
    MS-Off Ver
    Excel 365
    Posts
    340

    Re: Excel Round Up time to the nearest minute from total of 2 cells

    Thank You SO very much daddylonglegs for the help.... this works like a charm. Much appreciated!!

  5. #5
    Forum Contributor
    Join Date
    04-03-2012
    Location
    Washington State
    MS-Off Ver
    Excel 365
    Posts
    340

    Re: Excel Round Up time to the nearest minute from total of 2 cells

    Thanks shg... this works very well. Very much appreciated.

+ 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