Round “time” in Excel

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s