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|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|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|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|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|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|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|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|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>

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.

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

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

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

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

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