小伙伴們好，今天分享一個關于不規則時間轉換的小技巧。

這是Excel學員群里的學員提出的，我覺得這個案例很典型，所以分享給大家，希望能夠幫助在此方面有同樣困惑的小伙伴。

問題描述：計算A列學習時長的小時數

A列的時間非常的不規律，我們首先要轉換成標準的時間格式，才能進行計算。怎么轉換呢？推薦一個很好用的函數：SUBSTITUTE這個函數可以幫助我們將某個文本中的某些字符替換成新的字符。比如可以把 [h] 替換成冒號，把 [分] 替換成冒號，把 [秒] 替換成空。公式寫法如下：=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"h",":"),"分",":"),"秒","") 這個公式使用了3個SUBSTITUTE函數嵌套，實際上就是用第一個SUBSTITUTE函數的結果作為第二個SUBSTITUTE函數的第一參數，用第二個SUBSTITUTE函數的結果作為第三個SUBSTITUTE函數的第一參數。我們把公式拆分一下：result1 = SUBSTITUTE(A2,"h",":") ? ? ? ? ? ? ?// 替換 [h] 為冒號 result2 =SUBSTITUTE(result1,"分",":") ? ? ?// 替換 [分] 為冒號result3=SUBSTITUTE(result2,"秒","") ? ? ? ?// 替換 [秒] 為空最后得出的結果如下圖所示：看起來規整多了，但是這樣還不行，那些不包含小時和分鐘的時間明顯不對，此時我們需要把日期修正一下，即給缺少小時和分鐘的時間補上小時和分鐘。我們先來判斷哪些時間是缺少小時的，公式寫法如下：=FIND("h",A2)FIND函數可以幫助我們判斷A2單元格中是否包含 [h]，如果包含則返回[h]所在的位置，反之則返回錯誤值 [#VALUE!?]，如下圖所示：再來判斷哪些時間是缺少分鐘的，公式寫法如下：=FIND("分",A2)FIND函數可以幫助我們判斷A2單元格中是否包含 [分]，如果包含則返回[分]所在的位置，反之則返回錯誤值 [#VALUE!?]，如下圖所示：如果C列的結果是錯誤值，我們就在B列值的前面連接一個文本 [00:]，如果D列的結果為錯誤值，我們就在B列值的前面連接一個文本 [00:00:]，反之就顯示B列本身即可。公式寫法如下：=IF(ISERROR(FIND("分",A2)),"00:00:"&B2,IF(ISERROR(FIND("h",A2)),"00:"&B2,B2))這里用到了IF和ISERROR組合，如果FIND函數找不到[分]，則顯示["00:00:"&B2]，如果FIND函數找不到[h]，則顯示["00:"&B2]，否則顯示 B2本身。公式結果如下圖所示：修正好的結果貌似跟標準的時間還是有些許的差異，所以我們需要進一步整理，用CLEAN函數去除不可見字符，公式如下：=--CLEAN(E2)CLEAN函數用來清除不可見字符，就是那些看不到但又確實存在的字符，這些字符可能是系統內導出的數據自帶的。兩個短橫線是將文本型數字轉換成數字的一個小技巧，其實乘以1或者加上0也是可以的，就是讓文本型數字參與一次運算，但是運算結果不能改變其本身的值。日期和時間本質上也是數字，經過這樣處理后的結果如下圖所示：有了標準的時間，我們就可以輕松計算小時數了，公式如下：=HOUR(F2)+MINUTE(F2)/60+SECOND(F2)/60/60HOUR/MINUTE/SECOND函數分別用來獲取時間中的時分秒。分鐘轉換成小時要除以60，秒轉換成小時要除以3600，最終結果如下圖所示：最后可以把分步驟的公式合并在一起即可。今天的分享就到這里，你都學會了？光看不練假把式，不斷的練習才能促進知識的內化，我們下期再見！

更多Excel技能，歡迎您加入布衣公子網易云課堂《揭秘Excel真相》課程。

PPT課程地址：http://t.cn/Rm4oVdo

Excel課程地址：http://t.cn/Rm4oCLR

關聯閱讀：

二八法則如何在Excel中落地？|Excel133

棘手數據的合并可是PowerQuery的強項|Excel132

甘特圖，讓你的項目管理井井有條！|Excel131

用歷史數據預測未來趨勢，這個方法靠譜的多！|Excel130

自定義分組求平均，很有意思也很有挑戰的案例！|Excel129

移動平均是啥玩意？移動平均線怎么畫？|Excel128

這個下拉列表很另類，但做法超簡單！|Excel127

能根據輸入內容自動更新的下拉菜單你用過嗎？|Excel126

新、老、離職員工名單只要刷新一下就能輕松獲取|Excel125

如此不規則的數據是該好好整治整治了！|Excel124

批注操作，游刃有余，批量導出 So Easy!|Excel123

打開這篇文章之前，無法想象條件格式能帶給我們什么驚喜|Excel122

送一個提取不重復值的函數給您|Excel121

……

更多技能分享請您后臺回復「目錄」查看

福利關鍵詞：70、PNG、地圖、姜餅人、圖標、樣機、手、試看、試聽、封面、封底、排版、圖表、目錄頁、過渡頁、標題欄，后臺回復有驚喜哦！

布衣公子《揭秘Excel真相》課程原價299

前5000人惠顧僅需199元

單擊了解>>《揭秘EXCEL真相》課程詳情

▼

總結

以上是生活随笔為你收集整理的string.h包含哪些函数_多个函数组合拳专治不规则时间转化难题|Excel134的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。