blocks|key|1461906|text|既然你说你也想要日期，不如这样：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1461907|=+(ROUND((A1+*+1440)+/+15,+0)+*+15)+/+1440|offset|length|style|CODE|1461908|假设A1具有您想要的日期/时间值。这利用了Excel中的日期/时间列只是数字这一事实(整数部分是日期，小数部分是时间)|1461909|entityMap^0|0|0|16|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|M|8|@$D|N|E|O|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|P|8|@]|9|@]|A|$]]|$1|J|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|K|$]]

Since you said you also want the date, how about this:

<code>= (ROUND((A1 * 1440) / 15, 0) * 15) / 1440</code>

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)

blocks|key|1247089|text|如果要四舍五入到最接近的15：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1247090|假设您的时间是在单元格A2中|1247091|我们将把新的时间放到B2中：|1247092|B2+=TIME(HOUR(A2),+ROUND((MINUTE(A2)/60)*4,+0)+*+15,+0)|code-block|syntax|javascript|1247093|如果您希望始终向上或向下舍入，请将round替换为ROUNDUP或ROUNDDOWN|1247094|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|Q|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|R|8|@]|9|@]|A|$I|J]]|$1|K|3|L|5|6|7|S|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

If you want to round to the Nearest 15:

Assuming your time is in cell A2

We'll put our new time into B2:

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

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

blocks|key|1462257|text|mround将时间值作为字符串，因此您可以使用|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1462258|=mround(a1,"0:15")|code-block|syntax|javascript|1462259|我认为这是最短、最清晰的方法。|1462260|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

mround takes time values as strings, so you can use 

<pre><code>=mround(a1,"0:15")
</code></pre>

which I think is the shortest and clearest method.

blocks|key|1461873|text|将日期和时间四舍五入为最接近15分钟的时间段(您始终可以使用类似INT的内容向上/向下舍入)：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1461874|=DATE(YEAR(B1),MONTH(B1),DAY(B1))%2BTIME(HOUR(B1),+ROUND(MINUTE(B1)/15,0)*15,+0)|code-block|syntax|javascript|1461875|假设单元格B1包含要舍入的日期和时间。这将以典型的序列日期方式返回数字(例如39846.64444+=02/02/200915:28)，您需要将结果单元格格式化为日期/时间以查看该值(与此问题的所有解决方案一样)。同时显示日期和时间不是标准的日期或时间格式，您需要一个自定义格式才能做到这一点。|1461876|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

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

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

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.

blocks|key|1247361|text|刚刚发现了一个有用的函数MROUND，它在Excel2007中可用，也是一个分析插件。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1247362|假设你的时间是B2，像8.43这样的数字代表8小时25.8分钟：|1247363|=MROUND(MOD(B2,1)*60,15)|code-block|syntax|javascript|1247364|MOD(B2,1)将提取小数0.43；*60将转换为25.8；MROUND将四舍五入为15的最接近的倍数，即30。|1247365|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|N|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|O|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

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:

<pre><code>=MROUND(MOD(B2,1)*60,15)
</code></pre>

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.

blocks|key|1462146|text|假设您有两个日期时间，并且您想要找到总数并向上舍入到最接近的15分钟：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1462147|A1+=+7/10/2014+16:10++++
A2+=+7/10/2014+17:49|code-block|syntax|javascript|1462148|首先获取以十进制小时表示的总和：|1462149|A3:+++=(A2-A1)*24|1462150|然后，您可以四舍五入到最近的一刻钟：|1462151|A4:++=Ceiling(A3,+.25)|1462152|或者在一个单元格上完成所有操作：|1462153|A3:++=Ceiling((A2-A1)*24,+.25)|1462154|entityMap^0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|W|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|X|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|Y|8|@]|9|@]|A|$]]|$1|M|3|N|5|D|7|Z|8|@]|9|@]|A|$E|F]]|$1|O|3|P|5|6|7|10|8|@]|9|@]|A|$]]|$1|Q|3|R|5|D|7|11|8|@]|9|@]|A|$E|F]]|$1|S|3|-4|5|6|7|12|8|@]|9|@]|A|$]]]|T|$]]

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

<pre><code>A1 = 7/10/2014 16:10 
A2 = 7/10/2014 17:49
</code></pre>

First get the total in decimal hours:

<pre><code>A3: =(A2-A1)*24
</code></pre>

Then you can round up to the nearest quarter hour:

<pre><code>A4: =Ceiling(A3, .25)
</code></pre>

Or do it all on one cell:

<pre><code>A3: =Ceiling((A2-A1)*24, .25)
</code></pre>

blocks|key|1461810|text|如果时间在单元格A1中：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1461811|=ROUND(A1*(24*60/15),0)/(24*60/15)|code-block|syntax|javascript|1461812|(四舍五入到最接近的15分钟增量)|1461813|或|1461814|=INT(A1*(24*60/15),0)/(24*60/15)|1461815|(向下舍入到最后15分钟增量)|1461816|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|S|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|T|8|@]|9|@]|A|$]]|$1|K|3|L|5|D|7|U|8|@]|9|@]|A|$E|F]]|$1|M|3|N|5|6|7|V|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

If time is in cell A1:

<pre><code>=ROUND(A1*(24*60/15),0)/(24*60/15)
</code></pre>

(Rounding to nearest 15 minute increment)

or

<pre><code>=INT(A1*(24*60/15),0)/(24*60/15)
</code></pre>

(Rounding down to last 15 minute increment)

blocks|key|1247295|text|更简单?？|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1247296|=B2-模数(B2，15/24/60)|1247297|知道Excel+1天=+24小时=+1，|1247298|15/24/60+(=+0.0104166666666667)是相当于15分钟的数值。|1247299|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|J|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|K|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|L|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|M|8|@]|9|@]|A|$]]|$1|H|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|I|$]]

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.

blocks|key|1462064|text|这对我很有效|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1462065|=CEILING(((MOD(D16-C16;1))*24);0,25)|code-block|syntax|javascript|1462066|它首先以小时为单位计算两个不同时间C16和D16的时间差。然后四舍五入。|1462067|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

This worked for me 

<pre><code>=CEILING(((MOD(D16-C16;1))*24);0,25)
</code></pre>

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

blocks|key|1247507|text|保持简单。四舍五入到最接近的15分钟间隔。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1247508|=+ROUND(B2+*+24+*+4,+0)+/+4+/+24|code-block|syntax|javascript|1247509|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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

<pre><code>= ROUND(B2 * 24 * 4, 0) / 4 / 24
</code></pre>

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?

Round time to nearest 15min interval in Excel

我有一个excel电子表格，其中有一个列，其中包含特定事件的日期和时间。我想把它四舍五入到最近的15分钟间隔，这样我就可以计算这段时间内的事件总数。进行舍入的最好方法是什么？

问以Excel表示的最接近15分钟间隔的舍入时间
EN

回答 10

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问以Excel表示的最接近15分钟间隔的舍入时间EN

回答 10

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问以Excel表示的最接近15分钟间隔的舍入时间
EN