CODEWITHSUNDEEP
exceldatetimerounding
macleojw
macleojw

Reputation: 4159

Round time to nearest 15min interval in Excel

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?

Upvotes: 17

Views: 84746

Answers (11)

pooroldpedro
pooroldpedro

Reputation: 311

mround takes time values as strings, so you can use 

=mround(a1,"0:15")

which I think is the shortest and clearest method.

Upvotes: 4

IvanD
IvanD

Reputation: 8291

Keep it simple. Rounds to the closest 15 minute interval.

= ROUND(B2 * 24 * 4, 0) / 4 / 24

Upvotes: 0

Eric
Eric

Reputation: 21

Assuming you have 2 date-times and you want to find the total and round up to the nearest 15 minutes:

A1 = 7/10/2014 16:10    
A2 = 7/10/2014 17:49

First get the total in decimal hours:

A3:   =(A2-A1)*24

Then you can round up to the nearest quarter hour:

A4:  =Ceiling(A3, .25)

Or do it all on one cell:

A3:  =Ceiling((A2-A1)*24, .25)

Upvotes: 2

user3481381
user3481381

Reputation: 1

This worked for me 

=CEILING(((MOD(D16-C16;1))*24);0,25)

It calculates the time difference of two different times C16 and D16 in hours first. Then rounds up.

Upvotes: 0

SoteR
SoteR

Reputation: 71

simple:

  • if rounded up =CEILING(A1,"00:15")
  • if rounded down =FLOOR(A1, "00:15")

Where A1 is the difference between the two time. It can be a reference or calculations: =CEILING(C2-B2,"00:15"), where C2 is the end time and B2 is the start time.

Upvotes: 7

rajah9
rajah9

Reputation: 12329

Just found a helpful function, MROUND, which is available in Excel 2007 and as an Analysis add-in.

Assuming your time is in B2, with a number like 8.43 to represent 8 hours, 25.8 minutes:

=MROUND(MOD(B2,1)*60,15)

the MOD(B2,1) would extract the fractional 0.43; the *60 would convert to 25.8; and the MROUND would round you to the nearest multiple of 15, namely 30.

Upvotes: 3

iDevlop
iDevlop

Reputation: 25252

Simpler ??

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

Knowing that for Excel 1 day = 24 hrs = 1,
15/24/60 (= 0.0104166666666667) is the numeric equivalent equivalent of 15 min.

Upvotes: 0

Joel Goodwin
Joel Goodwin

Reputation: 5086

Date and time rounded to nearest 15-minute period (you can always round up/round down using something like INT):

=DATE(YEAR(B1),MONTH(B1),DAY(B1))+TIME(HOUR(B1), ROUND(MINUTE(B1)/15,0)*15, 0)

Assuming cell B1 contains the date time to be rounded. This will return the number in typical serial date fashion (e.g. 39846.64444 = 02/02/2009 15:28) and you need to format your result cell as a date/time to see the value (as with all solutions to this problem). Showing date and time together is not a standard Date or Time format, you need a Custom format to do this.

Upvotes: 3

David
David

Reputation: 25450

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)

Upvotes: 23

databyss
databyss

Reputation: 6488

If you want to round to the Nearest 15:

Assuming your time is in cell A2

We'll put our new time into B2:

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

If you wanted to always round up or down you replace ROUND with ROUNDUP or ROUNDDOWN

Upvotes: 7

JDunkerley
JDunkerley

Reputation: 12495

If time is in cell A1:

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

(Rounding to nearest 15 minute increment)

or

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

(Rounding down to last 15 minute increment)

Upvotes: 1

Related Questions