[數據處理] Excel 日期時間換算成秒 - 夢想與創造

可用下列函數達成。

Excel-時間相減後轉成秒數 =TEXT(A2-A1,"[ss]"). Thursday,March5,2020 [數據處理]Excel日期時間換算成秒 這次獻慶碰到Excel的數據處理問題。

儀器匯出的Excel檔的時間軸是用"日期跟時間"來表示，而不是用秒來表示。

這樣會造成繪圖軟體無法判斷時間，而無法將數據進行繪圖。

或者說很多繪圖軟體會把"日期跟時間"這種資料格式視作"字串"，而不是同單位的時間"數字"。

造成繪圖失效。

為了要處理這個問題。

一個單純的想法，就是將包含日期跟時間的文字換成單一時間單位。

這個時間單位，依據數據分析的需求可以是年、月、日、時、分、秒等。

案例1:2020/1/1512:59:35$\rightarrow$OOO秒 以獻慶這次的狀況將包含日期跟時間的文字換成秒，是合理處置。

如何將包含日期跟時間的文字換成秒? 2020/1/1512:59:35  -->  OOO秒 實際上，為了畫圖，我們要知道的是數據點間的相對時間，或者說時間差。

這個問題頗好解決、獻慶運氣不錯，很快就找到了網友提供的方式。

可用下列函數達成。

Excel-時間相減後轉成秒數 =TEXT(A2-A1,"[ss]") 可以強制把時間轉換為秒數。

若ss改為mm則轉換為分鐘，不足數無條件捨去。

參考資料: Excel-時間相減後轉成秒數 這個函數可用，但得小改一下。

要把A1改成第一筆資料的時間。

例如: =TEXT(A2-"2020/1/1512:59:34","[ss]") 這招有用，表示Excel有把某些特定日期跟時間的字串換成秒的功能。

這樣就不用像以前使用Fortran等程式時的做法，要用"天*86400+小時*3600+分*60+秒"這種數學換算。

案例2:2020/2/14下午06:00:49$\rightarrow$OOO秒 乍看之下，這組時間字串只是多了兩個字(上午/下午)而已。

多了兩個中文字而已，同樣的函數應該有用。

如果案例1的招式可以萬用的話，就不會有案例2了。

呵呵! 總之就是"掛了"! 那就要來找新的解決方案。

先講結論。

函數長這樣: =DATEVALUE(LEFT(A2,FIND("",A2)-1))+IF(COUNT(FIND("下午",  A2)),TIMEVALUE(RIGHT(A2,8))+0.5,0)+IF(COUNT(FIND("上午12",A2)),TIMEVALUE(RIGHT(A2,8))-0.5,0)+IF(COUNT(FIND("上午01",A2)),TIMEVALUE(RIGHT(A2,8)),0)+IF(COUNT(FIND("上午02",A2)),TIMEVALUE(RIGHT(A2,8)),0)+IF(COUNT(FIND("上午03",A2)),TIMEVALUE(RIGHT(A2,8)),0)+IF(COUNT(FIND("上午04",A2)),TIMEVALUE(RIGHT(A2,8)),0)+IF(COUNT(FIND("上午05",A2)),TIMEVALUE(RIGHT(A2,8)),0)+IF(COUNT(FIND("上午06",A2)),TIMEVALUE(RIGHT(A2,8)),0)+IF(COUNT(FIND("上午07",A2)),TIMEVALUE(RIGHT(A2,8)),0)+IF(COUNT(FIND("上午08",A2)),TIMEVALUE(RIGHT(A2,8)),0)+IF(COUNT(FIND("上午09",A2)),TIMEVALUE(RIGHT(A2,8)),0)+IF(COUNT(FIND("上午10",A2)),TIMEVALUE(RIGHT(A2,8)),0)+IF(COUNT(FIND("上午11",A2)),TIMEVALUE(RIGHT(A2,8)),0) 跟案例1也差太多了... 這種處理方式就真的是在處理字串，主要就是要把"上午/下午"拿掉，並把12時制換成24時制。

以下簡單講踢鐵板程序 Step1使用現成函數，試試看 使用網友提供的函數: =DATEVALUE(LEFT(A2,FIND("",A2)-1)) +IF(FIND("下午",A2),TIMEVALUE(RIGHT(A2,8))+0.5,TIMEVALUE(RIGHT(A2,8))) 參考資料: Excel-轉換含中文字的日期時間為數值格式 狀況:可以處理下午，但不能處理上午。

上午的數據會變成#VALUE! Step2尋找相關資訊、消化吸收、分析可能問題點 為了解決#VALUE!，找了一堆方法 參考資料: Excel尋找字串位置FIND函數用法教學與範例 Excel-搜尋多個字串(FIND) Excel-搜尋多個字串(FIND、SEARCH) ExcelIF系列函數用法教學：多條件判斷搭配AND、OR、NOT Excel-文字剖析(LEFT,RIGHT,MID,LEN) IF函數–巢狀公式及避免易犯的錯誤 儘管都是過了，但無效。

還是出現#VALUE! Step3 發現關鍵問題 會出現#VALUE!，主要來自於FIND這個函數。

對於"FIND不到"的狀況時，會回傳#VALUE! 解決方案，在FIND函數前面使用COUNT函數 IF(COUNT(FIND("下午",A2)),TIMEVALUE(RIGHT(A2,8))+0.5,0) 註:這裡的0.5是指12小時。

微軟在Excel中將一天訂成數值1，而下午的時間要由12時制變成24時制，得加上12小時，也就是要加上0.5。

參考資料: excel函数count的常见用法 Step4時間格式對齊 原始數據的"下午11:59"增加1分鐘到隔天時，會變成"上午12:00"，其實應該是"上午00:00"，否則會跟中午的12點衝突。

解決方案，將"上午12:00"的數據挑出來，減掉12小時(減0.5) IF(COUNT(FIND("上午12",A2)),TIMEVALUE(RIGHT(A2,8))-0.5,0)  其他的上午時間則不變 +IF(COUNT(FIND("上午01",A2)),TIMEVALUE(RIGHT(A2,8)),0)+IF(COUNT(FIND("上午02",A2)),TIMEVALUE(RIGHT(A2,8)),0)+IF(COUNT(FIND("上午03",A2)),TIMEVALUE(RIGHT(A2,8)),0)+IF(COUNT(FIND("上午04",A2)),TIMEVALUE(RIGHT(A2,8)),0)+IF(COUNT(FIND("上午05",A2)),TIMEVALUE(RIGHT(A2,8)),0)+IF(COUNT(FIND("上午06",A2)),TIMEVALUE(RIGHT(A2,8)),0)+IF(COUNT(FIND("上午07",A2)),TIMEVALUE(RIGHT(A2,8)),0)+IF(COUNT(FIND("上午08",A2)),TIMEVALUE(RIGHT(A2,8)),0)+IF(COUNT(FIND("上午09",A2)),TIMEVALUE(RIGHT(A2,8)),0)+IF(COUNT(FIND("上午10",A2)),TIMEVALUE(RIGHT(A2,8)),0)+IF(COUNT(FIND("上午11",A2)),TIMEVALUE(RIGHT(A2,8)),0) 註:其實"上午01"~"上午11"這段應該有更好的寫法。

例如，資料範圍判斷法，但是獻慶這裡就不多耗時間研究了。

整體功能性先出來，可以解決現階段問題就好。

Step5回到案例1方式處理 把"上午/下午"拿掉，並把12時制換成24時制後，就可以依照案例1的方式處理。

完工! 以下兩招讓案例1的數據處理招式更好用。

免掉複製第一點的時間字串麻煩，特別是整個實驗數據存在許多不同張工作表時。

1.為了要鎖定同張工作表中的特定儲存格數據。

函數範例:A2-->\$A\$2 參考資料: EXCEL公式設定方式：固定數值（又稱絕對值/絕對引用） 將一欄數字乘以相同數字 2.為了要鎖定某張工作表中的特定儲存格數據。

函數範例:A2-->address1!\$A\$2 參考資料: Excel-取用不同工作表中相同儲存格的值 感想 十幾二十年前，大部分的數據匯出都是全英文，還沒進到多國語言，日期時間轉換的問題還不是那麼嚴重。

就算不會轉換，有時運氣不錯。

有的數據很規律，就是每秒量測一個點，那也不用換算，直接使用"行數"當作秒數就行。

後來，慢慢碰到有些機台會有夾雜中文的資料，有時候用"行數"當作秒數，可以勉強處理掉，但若碰到數據有部分跳掉幾秒鐘、或跳掉幾分鐘、或是幾小時，那就難以處理了。

如果是獻慶自己的程式，那就好處理，在數據輸出時，就附上電腦中的秒數，沒有問題。

但總是會碰到別人給的數據、或是儀器直接匯出的數據，這些都難以請對方或廠商進行修改了。

很多時候，如果數據重要性沒那麼高，那也就算了。

但這次算是踢到鐵板了，閃不掉，那就要認真面對。

這次處理完成，也一起解決十多年前就掛在心裡的問題!收穫很多! GOOD!^_^ 感恩 眾多網友提供相關資料，還有函數範例，供參考。

才讓此問題可以順利解決。

(FBlink: https://www.facebook.com/hsienching.chung/posts/3165177583495189 ) 相關連結 為了處理這檔事，也找了很多其他資料，雖然相關性較低，但仍列出，做為參考。

Excel-計算兩個時間相差幾時幾分(TEXT) Excel-根據分數或秒數轉換為時:分:秒格式(TEXT) Excel計算兩個時間之間的差距教學 加上或減去時間 Excel-日期和時間的格式設定 Excel使用TEXT函數設定數字顯示格式用法教學與範例 Excel小教室–除錯高手「IFERROR」函數，幫你解決名稱錯誤、參數錯誤 現在的繪圖軟體對日期跟時間這樣的數據格式，支援度已經提升上來了。

就很大程度上可以避免此種問題的麻煩。

例如，使用OriginLab。

他們有自己的日期跟時間檔案格式，若符合該格式，則圖就很好畫出來了。

視情況可能需要格式調整:進去工作表後，選定Column，按右鍵選Properties。

ColumnProperties/Options/Format 選擇Date Origin'sDate-TimeSystem: https://www.originlab.com/doc/Origin-Help/WksCol-DateTime Postedby DreamandCreation at 9:09PM EmailThisBlogThis!SharetoTwitterSharetoFacebookSharetoPinterest Labels: Excel, 數據處理 Location: TaichungCity,Taiwan 1comment: DreamandCreationMarch5,2020at9:10PMGOOD!^_^ReplyDeleteRepliesReplyAddcommentLoadmore... NewerPost OlderPost Home Subscribeto: PostComments(Atom) Followers SubscribeTo Posts Atom Posts Comments Atom Comments Translate BlogArchive ►  2022 (14) ►  March (1) ►  February (8) ►  January (5) ►  2021 (13) ►  December (1) ►  November (1) ►  July (2) ►  June (7) ►  May (2) ▼  2020 (29) ►  November (1) ►  September (2) ►  August (5) ►  July (3) ►  May (2) ►  April (1) ▼  March (9) [笑話]兌不了獎的發票! [數據處理]Excel文字轉換成數字代碼 [家長二三事]組"類樂高玩具"8組 [鞋子]Zamberlan鞋子結構及技術簡介 [開箱]Zamberlan1013LeopardGTXWL防水高筒皮革重裝登山鞋綠迷彩... [學術研究]蒐集整本期刊 [數據處理]Excel日期時間換算成秒 [學術研究]碩士班時期的紙本研究筆記及數據圖 [學術研究]碩博士班時期，學長姐的紙本論文 ►  February (3) ►  January (3) ►  2019 (24) ►  December (1) ►  November (3) ►  October (3) ►  September (2) ►  August (1) ►  July (2) ►  June (3) ►  May (1) ►  April (4) ►  March (2) ►  February (2) ►  2018 (20) ►  December (1) ►  November (4) ►  October (2) ►  September (1) ►  July (1) ►  May (5) ►  April (5) ►  March (1) ►  2017 (164) ►  November (1) ►  October (2) ►  August (1) ►  June (5) ►  May (28) ►  April (31) ►  March (33) ►  February (29) ►  January (34) ►  2016 (190) ►  December (31) ►  November (35) ►  October (35) ►  September (30) ►  August (34) ►  July (19) ►  June (1) ►  March (1) ►  February (2) ►  January (2) ►  2015 (16) ►  October (1) ►  July (2) ►  May (1) ►  March (2) ►  February (2) ►  January (8) ►  2014 (25) ►  December (8) ►  November (8) ►  September (1) ►  August (4) ►  July (2) ►  June (1) ►  May (1) Keywords Arduino (8) Chromecast (1) eSpring (3) Excel (2) Greenpeace (1) LogoDesign (3) Music (1) STM32 (1) VisitingCardDesign (2) Warrantycarddesign (1) YouTube (1) Zamberlan (1) 保養 (1) 修理 (5) 健康 (6) 儀器學習 (3) 分享 (17) 創造 (4) 台灣 (2) 台灣電池協會 (3) 名片設計 (2) 咖啡 (13) 售後服務卡設計 (1) 商業設計 (1) 商標設計 (3) 問題 (14) 夢想 (24) 學習 (44) 學術書籍 (1) 家庭 (24) 家長二三事 (5) 展覽 (1) 店面設計 (2) 思考問題 (7) 感恩 (44) 挑戰 (3) 支持 (10) 故障 (1) 救車 (5) 數據處理 (2) 文化 (3) 日文 (3) 日文學習 (9) 日文歌詞 (311) 會議 (10) 朋友 (17) 服務 (7) 照明 (7) 營養 (6) 物理 (22) 生命中的寶藏 (3) 生活 (7) 登山 (4) 研究 (38) 祝福 (24) 笑話 (3) 緬懷 (1) 美食 (1) 老物 (1) 能源安全 (2) 能源技術 (7) 能源產業 (9) 著作 (2) 著作權 (1) 蛋白質 (1) 觀光 (1) 讀書會 (1) 跳舞的羊 (1) 進度 (1) 遊戲 (1) 運動 (6) 部落格慶祝 (2) 鋰鐵電池 (5) 鍾安得 (6) 開瓶 (1) 開箱 (3) 關於這個部落格 (4) 電影 (1) 鞋子 (1) 音樂 (7) 飲食日記 (4) 體重控制 (5) AboutMe DreamandCreation Viewmycompleteprofile TotalPageviews PopularPosts 什麼是電池的CCA(ColdCrankingAmperes)?如何量測CCA? 前陣子車子進廠保養，隔天保養回來後，上面夾了張小紙條。

電瓶檢測報告，測試結果良好，電壓12.6V，內阻6.21mΩ，壽命100%，CCA為439。

獻慶好奇的是，啥是CCA? 如何量測CCA?電瓶檢測報告以下將會一一討論什麼是CCA?... [教學][產品安裝]GoogleChromecast正規安裝and非正規安裝 這幾年來，家長們也都紛紛開始使用手機，而且用量越來越大。

問到最常拿來看影片的APP，還是以YouTube為大宗。

看著家長們拿著小手機在看影片，畫面小、聲音也小，獻慶有點不忍啊!於是就找到了GoogleChromecast這個可以投放YouTube... 關於數位電導電池測試器(或稱電瓶測試器/壽命分析儀/電瓶檢測儀/電壓表/CCA表/內阻表)的量測原理及適用範圍 數位電導電池測試器，市面上或稱電瓶測試器、壽命分析儀、電瓶檢測儀、電壓表、CCA表、內阻表一個儀器可以有這麼多"亂七八糟"的名字，表示大眾對這個東西的認知很模糊。

在認識這個儀器的過程中，很多亂七八糟的資訊，眾說紛紜，也讓獻慶亂了一陣子。

... [開箱][升級]技嘉GIGABYTEAERO15 2017-06-26獻慶由於科學研究需要，去買了台技嘉GIGABYTEAERO15。

以下為簡易開箱、升級(+RAM+SSD)、兼操過一陣子的感想。

開箱照例得來些照片瓦楞紙箱中裝著(1)筆電、(2)雪地口味的筆電背包、(3)技... [2015-02-19]大年初一回憶台中市晨跑 2015-02-19大年初一昨晚除夕夜，就決定大年初一來個晨跑，希望新的一年都能這樣的有活力。

當然，也仔細看看好久沒見的台中市。

路線介紹:這個晨跑，一開始沿著美術園道，經過一些親朋好友家，還有一些最近去過的幾家餐廳。

繞過市民廣場後，到達科博館及植...