如何將「2小時47分49秒」轉換為多少秒？

01-28

「2小時47分49秒」是字元串，要將其轉換為多少秒，最常見的思路時用字元串函數分別提取出2、47，然後分別乘以3600、60，然後再加上字元串中的秒數，這是很常規的一種解法，那有沒有另外的方法呢？

在介紹其他的解法之前，我們先來看一下使用Excel時遇到的一種情況：

我們在單元格中輸入文本字元串「2017年3月3日」，Excel會自做聰明的變成「2017-3-3」，並顯示為「「2017年3月3日」，如果輸入2017年3月「，它會自做聰明的變為「2017-3-1」。

同理，如果，我們輸入」5時37分7秒「字元串，它會自動變為」5:37:07"，並顯示為」5時37分7秒「，如果輸入」7時28分「，它會自動變為」7:28:00"，並顯示為」7時28分「。

看到這裡，我們應該明白了，如果我們輸入日期或時間字元串，它就自動將其轉換為標準的日期或時間，並按輸入的格式顯示。也就是說，只要是標準格式的日期或時間，Excel是可以將其轉為標準的日期或時間的，那在公式中，能不能轉換呢？

我們在A2單元格輸入2時47分49秒，回車後，其自動變為「2:47:49」，用ISTEXT函數測試，計算結果為FALSE，說明它不是文本。

我們在A3輸入公式="2時47分49秒"，回車後，它靠左顯示，文本一般是靠左顯示的，再用ISTEXT函數測試，計算結果為TRUE，說明它是文本。

然後，我們將A3的公式修改為=--"2時47分49秒"，也就是在文本字元串前加二個負號（負負得正），這個用法，我們一般是用於將文本數字轉換為真正的數值。回車後，A3的計算結果顯示為0.116539352。我們知道，在Excel中日期和時間本質上就是數字，系統最早的日期是1900年1月1日，它是1，今天是2017年3月3日，實際上就是42796，也就是說從1900年1月1日到今天共42796天。一天24小時，一天是1，那麼凌晨1點就是0.041667（1/24），中午是12時，剛好半天，也就是0.5。

我們將A3單元格設為A2單元格一樣的時間格式，就可看到，它顯示的和A2單元格一模一樣。

因而我們可以得出結論，在公式中，字元串的日期和時間，也是可以轉換為標準的日期和時間的。

所以，「2小時47分49秒」這種非標準的時間，我們只要將「小」字刪除（可用SUBSTITUTE刪除「小」），然後在前面加兩個負號（負負得正）就可轉換為標準時間，然後再乘以86400（24*60*60）即可。

轉換的公式很簡單：

=--SUBSTITUTE(A2,"小",)*86400n

前面的那種情況用上面的公式沒問題，但如果下面這種情況，還是套用此公式就會了錯

由於原數據有些時間並沒有小時，甚至沒有分，那麼對文本做算術運算時（添加負號）就會出錯，為避免這種情況，我們先刪除「小」，然後在原數據前依次加上"0時0分"、"0時"、0，添加後補全，將其轉換為負數，這時那些添加字元後如果是非標準時間，轉換為負數時就會出錯，然後用LOOKUP查找0，利用LOOKUP會忽略錯誤值特點，將錯誤值忽略掉，再次乘以負數和86400，就會得出正確結果。完整的公式為：

=-LOOKUP(,-({"0時0分","0時",0}&SUBSTITUTE(A2,"小",)))*86400

上面公式編製思路的完整解釋參見下圖

C2、C5、C8是給時間字元串前添加"0時0分"，公式為：="0時0分"&B2

C3、C6、C9是給時間字元串前添加"0時"，公式為：="0時"&B3

C4、C7、C10是給時間字元串前添加0，公式為：=0&B4

關於LOOKUP函數的詳細解釋請參見本人博客的下面二篇文章：

深入理解LOOKUP：LOOKUP函數的查找原理_龍逸凡_新浪博客

公式-LOOKUP(1,-LEFT(A1,ROW($1:$10)))詳解_龍逸凡_新浪博客

--------------------------------------------------------------------------------------

歡迎大家加入《「偷懶」的技術：打造財務Excel達人》讀者交流QQ群305642687，探討用Excel巧妙偷懶的技術。

本文首發於微信公眾號：Excel偷懶的技術

本公眾號堅持分享原創Excel技巧文章，求實用、接地氣，不炫技，歡迎大家關注。