Rounding Time

By in Uncategorized on .

Excel stores dates as the number of dates since December 31, 1899 unless you’re using the 1904 date option. Times are stored as decimals representing the fraction of a day. 12:00PM is stored as .5, for example. You can use this information to round times, say, to the nearest minute by converting the decimal to something that can be reliably rounded.

I usually start by creating a defined name to hold the conversion constant. The conversion constant is 86,400 or 24 hours x 60 minutes x 60 seconds. This results in the number of seconds since Dec. 31, 1989. Here’s the defined name that I set up:

RoundTime1

You can use this defined name in a formula, like this:

RoundTime2

Of course you could define the name as =24*60, but I often find that I need 86,400 in various places. I usually just define it that way and do any other specific conversions in the formula. In the above formula, I divide by 60 so that the integer portion is the minutes and the decimal portion is the seconds. Rounding to zero places rounds to the nearest minute.

Note also that the result is an integer representing the number of minutes. In some cases, you may want the result to be a time. In this example, I convert the time, round it, and covert it back to a time. The cells are formatted as time.

RoundTime3

4 thoughts on “Rounding Time

  1. ross

    Yeah good, but i think i’ll just stick to =MINUTE(NOW()) etc, just seems easier. Can’t think of a good reason to change.

  2. Edward Kam

    The Rounding Time solution is easy to follow but it seems that I’m unable to get the required results. In either of the interger or time formula, I would get 0:00. Even I changed the cell formatting either. Any advise? Thanks.

  3. Jitender Virmani

    Edward Kam – you missed the second conversion that’s why you are getting 0:00.
    I was also getting the same, but after second conversion and using cell formatting to time.
    Formula goes like this =ROUND(A5*(24*60),0)/(24*60)

  4. Jitender Virmani

    Edward Kam – you missed the second conversion that’s why you are getting 0:00.
    I was also getting the same, but after second conversion and using cell formatting to time, it worked well
    Formula goes like this =ROUND(A5*(24*60),0)/(24*60)

Leave a Reply

Your email address will not be published. Required fields are marked *

To create code blocks or other preformatted text, indent by four spaces:

    This will be displayed in a monospaced font. The first four 
    spaces will be stripped off, but all other whitespace
    will be preserved.
    
    Markdown is turned off in code blocks:
     [This is not a link](http://example.com)

To create not a block, but an inline code span, use backticks:

Here is some inline `code`.

For more help see http://daringfireball.net/projects/markdown/syntax