Rounding Time

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 Comments

  1. ross says:

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

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

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

    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)

Posting code or formulas in your comment? Use <code> tags!

  • <code lang="vb">Block of code goes here</code>
  • <code lang="vb" inline="true">Inline code goes here</code>
  • <code>Formula goes here</code>

Leave a Reply

Here's how to update your reports of company and nearly any web data: