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)
= 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!