The “Time” format is one of those least understood formats, at least used to be for me! Until, I had to work on it extensively for one of my projects.. And it’s actually not that complex.. :)

Here’s a quick snapshot on how the Time format works -

- The DATE & TIME is integrated to be represented by one number – a floating point number!

- The decimal part characterizes the time, while the integer part the date.

- [0,1] = [00:00 HRS,00:00 HRS + 1 day] This is the set that determines the time of the day. For eg, 0.5 = 12 NOON.

- Hence, “1/1/2012 12:00:00 PM” is nothing but “40909.5″

- While working with manipulations over time data, understanding the underlying number structure proves critical to work around it. Some eg.s include rounding time to the nearest hour/half hour or quarter hour.. Or finding the difference in time between one day and the next.. etc..

Now that the background’s covered, let’s jump in into the post’s topic – Rounding Time in Excel!

Here’s a list of formulae that do the trick -

= (ROUND((A1 * 1440) / 15, 0) * 15) / 1440

= TIME(HOUR(A2), ROUND((MINUTE(A2)/60)*4, 0) * 15, 0)

= ROUND(A1*(24*60/15),0)/(24*60/15)

= INT(A1*(24*60/15),0)/(24*60/15)

= B2-MOD(B2,15/24/60)

= MROUND(MOD(B2,1)*60,15) (Note: Excel 2007 Addin – Not tested)

All of these work, and they resort to using the decimal numbers to get to the TIME manipulations. Have a go at some of these formula, and you might be able to understand how it works!