在日常工作中，通話時(shí)長的計(jì)算，或者是商業(yè)區(qū)停車時(shí)長的計(jì)算收費(fèi)等，都是非常常見的。
它們有的是按分鐘收費(fèi)。
比如：電信通話時(shí)長，不足 1 分鐘按 1 分鐘計(jì)算。
有的是按半小時(shí)或者 1 小時(shí)計(jì)算。
比如：商業(yè)區(qū)停車收費(fèi)，不足半小時(shí)或者不足 1 小時(shí)就全部按半小時(shí)或者 1 小時(shí)來計(jì)算收費(fèi)等等。
今天，我們就來分享下按時(shí)間長度計(jì)算的問題與解決思路。
1、問題要求
如下圖，是一張時(shí)長計(jì)算表：
要求是：計(jì)算開始時(shí)間與結(jié)束時(shí)間之間的分鐘數(shù)。不足一分鐘按一分鐘計(jì)算。
比如：
開始時(shí)間是 10 時(shí) 30 分 35 秒；
結(jié)束時(shí)間是 10 時(shí) 38 分 59 秒；
之間的時(shí)間差是：8 分 24 秒；
不足一分鐘按一分鐘計(jì)算，最后結(jié)果即為：9 分鐘。
2、解題方法
從上面的要求來看，解題思路大概可以分為以下兩種：
▋方法 1
通常情況下，最為簡單的而且是最好理解的是使用時(shí)間函數(shù)來處理。
比如：Hour 取小時(shí)數(shù)，Minute 取分鐘數(shù)，Second 取秒數(shù)。
如下圖：我們可以在【D3：D6】單元格輸入如下公式：
=HOUR(C3-B3)*60+MINUTE(C3-B3)+(SECOND(C3-B3>0)
公式解析：
? 用【C3】的結(jié)束時(shí)間減去【B3】的開始時(shí)間，然后用 Hour 函數(shù)取出小時(shí)數(shù)并乘以 60，得出分鐘數(shù)。
? 用【C3】的結(jié)束時(shí)間減去【B3】的開始時(shí)間，然后用 Minute 函數(shù)取出分鐘數(shù)，直接得出分鐘數(shù)。
? 用【C3】的結(jié)束時(shí)間減去【B3】的開始時(shí)間，然后用 Second 函數(shù)取出秒數(shù)并判斷是否大于 0，如果大于 0，就返回 TRUE，用加法讓其變成 1 分鐘。如果小于等于 0，返回 False，用加法讓其變成 0 分鐘。
從而實(shí)現(xiàn)了不足 1 分鐘，按 1 分鐘計(jì)算的目的。
▋方法 2
其實(shí)，方法 1 只是從原理上來計(jì)算時(shí)間長度。
我們也可以換用專門用于計(jì)算進(jìn)位的函數(shù)（Ceiling 函數(shù)）來輕松解決此類問題。
它的語法如下：
=CEILING(number, significance)
=CEILING (要舍入的值，倍數(shù))
翻譯成中文就是：
將要舍入的值向上舍入到指定倍數(shù)。
那么，上面的計(jì)算可以改成：
【D3】公式如下：
=CEILING(C3-B3,TIME(0,1,0))*1440
公式解析：
先用結(jié)束時(shí)間減去開始時(shí)間，之后按照一分鐘的倍數(shù)向上舍入，之后再乘以 1440 分鐘。
其中：Time 函數(shù)是表示時(shí)間的函數(shù)。
它的語法如下：
=Time (小時(shí)，分鐘，秒)
這里是要按 1 分鐘為倍數(shù)，所以小時(shí)和秒都為 0。
這里的 1440 是指：1 天為 24 小時(shí)，1 小時(shí)為 60 分鐘，即：24*60=1440
此處的公式還可以進(jìn)一步簡化，直接改寫為如下圖：
=CEILING((C3-B3)*1440,1)
公式解析：
先用結(jié)束時(shí)間減去開始時(shí)間，之后乘以 1440 分鐘數(shù)，然后按 1 的倍數(shù)向上舍入到整數(shù)。
兩種解決思路供大家參考。
3、知識(shí)擴(kuò)展
? 在日常工作中，我們可能還會(huì)接觸到使用 Text 函數(shù)來獲取和處理時(shí)間。
如下圖：
公式：
=TEXT(C3-B3,"[m]")
先用結(jié)束時(shí)間減去開始時(shí)間，之后用 Text 函數(shù)取出之間的分鐘數(shù)。
但是，小伙伴們要特別注意下：
這里用 Text 函數(shù)取出分鐘數(shù)，它是直接截尾取整數(shù)。
也就是它只管小時(shí)和分鐘數(shù)，而不管秒數(shù)有或者沒有，都將其忽略不計(jì)。
另外：大家在取分鐘數(shù)的時(shí)候千萬不要只寫一個(gè) M。
如下圖：
公式如下：
=TEXT(C3-B3,"m")
此時(shí)如果只寫一個(gè)字母 M 的話，它只會(huì)取出兩個(gè)時(shí)間相減的月份數(shù)，而不是分鐘數(shù)。
這里的時(shí)間對應(yīng)的日期默認(rèn)為 1900 年 1 月 0 日。
所以取其月份數(shù)，結(jié)果返回 1。
? 在使用 Ceiling 函數(shù)的時(shí)候，有可能會(huì)出現(xiàn)下面的錯(cuò)誤結(jié)果。
如下圖：
公式與上面一模一樣：
=CEILING((C3-B3)*1440,1)
結(jié)束時(shí)間減去開始時(shí)間應(yīng)該為 10 分鐘，但是結(jié)果卻是錯(cuò)誤的 11 分鐘。
我們可以看下 Ceiling 函數(shù)里面的第一參數(shù)的運(yùn)算結(jié)果，如下圖：
里面的運(yùn)算結(jié)果產(chǎn)生了一個(gè)極小的尾差，所以導(dǎo)致向上舍入到 11 分鐘了。
解決方法可以用 Round 函數(shù)進(jìn)行舍入處理。
修正公式如下：
=CEILING(ROUND((C3-B3)*1440,6),1)
這里用 Round 函數(shù)保留 6 位小位，超過 6 位的，直接四舍五入。
最后結(jié)果顯示正確！
4、寫在最后
今天，我們分享了日常工作中計(jì)算時(shí)間長度的常用方法與思路。
? 可以使用最基礎(chǔ)的時(shí)間函數(shù)來處理時(shí)長問題。比如：Hour\Minute\Second 等。
? 也可以使用微軟專門針對此問題設(shè)計(jì)的專用函數(shù) Ceiling 函數(shù)來處理。
只不過在處理的時(shí)候，大家要特別注意上面提到的尾差問題，要不然很容易導(dǎo)致出錯(cuò)。
本文來自微信公眾號：秋葉 Excel （ID：excel100），作者：明鏡在心
廣告聲明：文內(nèi)含有的對外跳轉(zhuǎn)鏈接（包括不限于超鏈接、二維碼、口令等形式），用于傳遞更多信息，節(jié)省甄選時(shí)間，結(jié)果僅供參考，IT之家所有文章均包含本聲明。