設(shè)置
  • 日夜間
    隨系統(tǒng)
    淺色
    深色
  • 主題色

用 Excel 解決計(jì)時(shí)問題

秋葉Excel 2023/10/22 11:45:00 責(zé)編:夢澤

在日常工作中,通話時(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之家所有文章均包含本聲明。

相關(guān)文章

關(guān)鍵詞:Excel教程,Excel學(xué)院

軟媒旗下網(wǎng)站: IT之家 最會(huì)買 - 返利返現(xiàn)優(yōu)惠券 iPhone之家 Win7之家 Win10之家 Win11之家

軟媒旗下軟件: 軟媒手機(jī)APP應(yīng)用 魔方 最會(huì)買 要知