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?
我有一个excel电子表格，列中有特定事件的日期和时间。我想把它四舍五入到最近的15分钟间隔，这样我就可以计算出这段时间内事件的总数。做舍入的最好方法是什么?
11 个解决方案
#1
22
Since you said you also want the date, how about this:
既然你说你也想约会，那这个呢?
= (ROUND((A1 * 1440) / 15, 0) * 15) / 1440
=(圆(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)
假设A1有你想要的日期/时间值。这利用了Excel中的日期/时间列只是数字这一事实(整数部分是日期，小数部分是时间)
#2
7
If you want to round to the Nearest 15:
如果你想四舍五入到最近的15:
Assuming your time is in cell A2
假设你的时间在细胞A2中
We'll put our new time into B2:
我们把新的时间放到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
如果你想总是上下移动，你可以用ROUNDUP或ROUNDDOWN来代替。
#3
6
simple:
简单:
- if rounded up =CEILING(A1,"00:15")
- 如果围捕=上限(A1,“今日”)
- if rounded down =FLOOR(A1, "00:15")
- 如果是圆形的=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.
其中A1是两个时间的差值。它可以是一个引用或计算:=上限(C2-B2，“00:15”)，其中C2是结束时间，B2是开始时间。
#4
3
Date and time rounded to nearest 15-minute period (you can always round up/round down using something like INT):
日期和时间四舍五入到最近的15分钟周期(您总是可以使用整数或整数):
=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.
假设单元格B1包含要圆的日期时间。这将以典型的串行日期方式返回数字(例如39846.64444 = 02/2009 15:28)，您需要将结果单元格格式化为一个日期/时间来查看值(与此问题的所有解决方案一样)。一起显示日期和时间不是标准的日期或时间格式，您需要自定义格式来实现这一点。
#5
3
Just found a helpful function, MROUND, which is available in Excel 2007 and as an Analysis add-in.
刚刚找到了一个有用的函数MROUND，它在Excel 2007中是可用的，并且可以作为一个分析插件。
Assuming your time is in B2, with a number like 8.43 to represent 8 hours, 25.8 minutes:
假设你的时间在B2中，数字8.43代表8小时，25.8分钟:
=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.
对(B2,1)取0。43;*60会变成25.8;MROUND的圈数是15的最近倍数，也就是30。
#6
3
mround takes time values as strings, so you can use
mround接受时间值作为字符串，因此可以使用
=mround(a1,"0:15")
which I think is the shortest and clearest method.
我认为这是最简短最清晰的方法。
#7
2
Assuming you have 2 date-times and you want to find the total and round up to the nearest 15 minutes:
假设你有2个约会时间，并且你想要找到约会总时间，最多15分钟:
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)
#8
1
If time is in cell A1:
如果时间在单元格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)
(增加15分钟)
#9
0
Simpler ??
简单? ?
=B2-MOD(B2,15/24/60)
= 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.
对于Excel 1天= 24小时= 1,15/24/60(= 0.010416666666666666)，数值等效为15分钟。
#10
0
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.
它首先在小时内计算两个不同的时间间隔C16和D16。然后围捕。
#11
0
Keep it simple. Rounds to the closest 15 minute interval.
保持简单。以15分钟为间隔。
= ROUND(B2 * 24 * 4, 0) / 4 / 24
#1
22
Since you said you also want the date, how about this:
既然你说你也想约会，那这个呢?
= (ROUND((A1 * 1440) / 15, 0) * 15) / 1440
=(圆(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)
假设A1有你想要的日期/时间值。这利用了Excel中的日期/时间列只是数字这一事实(整数部分是日期，小数部分是时间)
#2
7
If you want to round to the Nearest 15:
如果你想四舍五入到最近的15:
Assuming your time is in cell A2
假设你的时间在细胞A2中
We'll put our new time into B2:
我们把新的时间放到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
如果你想总是上下移动，你可以用ROUNDUP或ROUNDDOWN来代替。
#3
6
simple:
简单:
- if rounded up =CEILING(A1,"00:15")
- 如果围捕=上限(A1,“今日”)
- if rounded down =FLOOR(A1, "00:15")
- 如果是圆形的=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.
其中A1是两个时间的差值。它可以是一个引用或计算:=上限(C2-B2，“00:15”)，其中C2是结束时间，B2是开始时间。
#4
3
Date and time rounded to nearest 15-minute period (you can always round up/round down using something like INT):
日期和时间四舍五入到最近的15分钟周期(您总是可以使用整数或整数):
=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.
假设单元格B1包含要圆的日期时间。这将以典型的串行日期方式返回数字(例如39846.64444 = 02/2009 15:28)，您需要将结果单元格格式化为一个日期/时间来查看值(与此问题的所有解决方案一样)。一起显示日期和时间不是标准的日期或时间格式，您需要自定义格式来实现这一点。
#5
3
Just found a helpful function, MROUND, which is available in Excel 2007 and as an Analysis add-in.
刚刚找到了一个有用的函数MROUND，它在Excel 2007中是可用的，并且可以作为一个分析插件。
Assuming your time is in B2, with a number like 8.43 to represent 8 hours, 25.8 minutes:
假设你的时间在B2中，数字8.43代表8小时，25.8分钟:
=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.
对(B2,1)取0。43;*60会变成25.8;MROUND的圈数是15的最近倍数，也就是30。
#6
3
mround takes time values as strings, so you can use
mround接受时间值作为字符串，因此可以使用
=mround(a1,"0:15")
which I think is the shortest and clearest method.
我认为这是最简短最清晰的方法。
#7
2
Assuming you have 2 date-times and you want to find the total and round up to the nearest 15 minutes:
假设你有2个约会时间，并且你想要找到约会总时间，最多15分钟:
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)
#8
1
If time is in cell A1:
如果时间在单元格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)
(增加15分钟)
#9
0
Simpler ??
简单? ?
=B2-MOD(B2,15/24/60)
= 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.
对于Excel 1天= 24小时= 1,15/24/60(= 0.010416666666666666)，数值等效为15分钟。
#10
0
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.
它首先在小时内计算两个不同的时间间隔C16和D16。然后围捕。
#11
0
Keep it simple. Rounds to the closest 15 minute interval.
保持简单。以15分钟为间隔。
= ROUND(B2 * 24 * 4, 0) / 4 / 24