Menu
I've an excel spreadsheet with a column which has the date and time of a particular event. I would like to round this to the nearest 15 minute interval so that I can count the total number of events in this period. What is the best way to do do the rounding?
727
so if you want to round to 15 mins - there are 24 * 4 = 96 lots of 15 minutes in one day. so multiply the date by 96, then round, then divide by 96 should do the trick.
1. Enter this formula: =MROUND(A2,"0:15") or =MROUND(A2,"0:30") into a blank cell beside your data which need to round to nearest quarter or half hour, and then drag the fill handle down to the cells you want to apply this formula, and you will get a list of decimal numbers, see screenshot: 2.
To round a number down to nearest 0.5, use the FLOOR function, for example =FLOOR(A2, 0.5) . To round a number up to nearest 0.5, use the CEILING function, for example =CEILING(A2, 0.5) . To round a number up or down to nearest 0.5, use the MROUND function, e.g. =MROUND(A2, 0.5) .
Tip: You can also add up times by using the AutoSum function to sum numbers. Select cell B4, and then on the Home tab, choose AutoSum. The formula will look like this: =SUM(B2:B3). Press Enter to get the same result, 16 hours and 15 minutes.
Since you said you also want the date, how about this:
= (ROUND((A1 * 1440) / 15, 0) * 15) / 1440
Assuming that A1 has the date/time value you want. This takes advantage of the fact that date/time columns in Excel are just numbers (integer portion is the date, fractional portion is the time)
59
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
