**How does the Time format work in Excel?**

– 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”

**What kind of functions would we need to run on Time formatted data?**

Here’s 2 examples –

– Rounding time to the nearest hour, half hour or quarter hour (for eg. converting running time into buckets of 15 min intervals)

– Finding the difference in time between one day and the next (How many hours has elapsed between onset time and end time?)

**Below is a set of syntax to round time to the nearest 15 min window.**

**= (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.

### Like this:

Like Loading...

*Related*