excel函數公式應用：時間日期提取公式匯總，你用過哪些？

2020-10-20 部落窩教育BLW

編按：哈嘍，大家好！如何快速在一組時間數據中分別提取出年月日、時分秒數據？如何快速計算某日期是年內第幾周、星期幾，以及日期之間間隔的天數、月數、年數、小時數、分鐘數？如何快速補全指定月份日期，合併日期和時間？今天老菜鳥針對上述在日常工作中經常會遇到的問題，總結了20個常用的關於日期和時間的公式，趕緊來看看吧！學習更多技巧，請收藏關注。

在實際工作中，經常需要進行日期和時間的計算，如：工作日天數、入職天數、合同到期日期、員工生日提醒、計算加班時間........

如果用人工計算會非常麻煩，而使用Excel函數公式則非常簡單，今天給大家整理一期時間計算的公式套路大全，記得收藏起來慢慢看！（本教程涉及的公式都比較基礎，不做過多講解，需要哪個公式直接套用即可。）

第一類公式：拆分類（共11個）

如下圖所示，數據源為系統導出的格式，在這種數據源中，日期與時間是同時存在的，對於這種數據源來說，可以從中獲取對應的日期、時間，進而獲得年、月、日、時、分、秒以及年內周數，周內天數以及星期幾等。

公式1：拆分日期

使用公式=INT(A2)得到日期，並修改單元格格式。

公式2：拆分時間

使用公式=MOD(A2,1)得到時間，並修改單元格格式。

公式3-5：獲取年月日

提取年的公式：=YEAR(A2)

提取月的公式：=MONTH(A2)

提取日的公式：=DAY(A2)

公式6-8：獲取時分秒

提取時的公式：=HOUR(A2)

提取分的公式：=MINUTE(A2)

提取秒的公式：=SECOND(A2)

公式9：年內第幾周

公式=WEEKNUM(A2)可以得到一個數字，該數字表示日期在這一年的第幾周。

WEEKNUM的應用場景：在某些場合，可能需要按周來進行銷售分析，而如果數據中只有日期，此時就可以用WEEKNUM函數來輔助，再用透視表得到每周的匯總數據，如下圖所示。

公式10-11：周內第幾天和星期幾

先來看星期幾的公式：=TEXT(A2,"aaaa")

"aaaa"是TEXT函數中的星期代碼，關於TEXT函數之前有很多教程，不熟悉的小夥伴可以查看文章《如果函數有職業，TEXT絕對是變裝女皇！》，這裡就不贅述了。

需要說明的是星期幾和周內第幾天之間的區別。

通常可以使用公式=WEEKDAY(A2,2)得到數字所表示的星期幾，如圖所示。

然而這個公式的本質卻並不是計算星期幾，第二參數2表示用星期一作為一周的第一天，按照這個規則來確定日期是本周的第幾天。學習更多技巧，請收藏關注。

WEEKDAY的應用場景：常見於對考勤統計中周末的判定，主要是星期六和星期天，公式=WEEKDAY(A2,2)>5可以直接得到判定結果，進而作為具體統計的條件使用，也可以作為條件格式設置中的條件使用。

第二類公式：合併類（共2個）

公式12：日期與時間合併

這個很簡單，兩個單元格相加後再設置單元格格式就行了。

自定義格式的代碼為yyyy/m/d h:mm:ss

公式13：指定月份補全日期

這種問題常見於考勤表中，指定月份就能得到該月的日期列表。

這類問題通常使用DATE函數來補全日期，例如公式=DATE(2020,$A$2,COLUMN(A1))可以實現下圖所示的效果。

第三類公式：時間差和日期差（共5個）

公式14-15：計算時間間隔小時數、分鐘數

要計算兩個時間之間的間隔小時，可以使用公式：=(B2-A2)*24

要計算兩個時間之間的間隔分鐘，可以使用公式：=(B2-A2)*1440

公式16-18：計算兩個日期之間的天數、月數和年數

間隔天數：=B9-A9

間隔月數：=DATEDIF(A9,B9,"M")

間隔年數：=DATEDIF(A9,B9,"Y")

間隔天數可以用兩個日期直接相減，間隔月數和間隔年數可以用DATEDIF函數得到，不熟悉這個函數的小夥伴可以查看這篇文章《》。

第四類公式：格式轉換類（共2個）

格式轉換是指8位數字和日期之間的互換，常用於各類系統數據導出或導入時。

公式19：8位數字轉換為日期

=--TEXT(A16,"0-00-00")

說明：TEXT前的--是為了將文本變成數值，如果不加--的話，得到的只是類似日期的一種文本結果。

公式20：日期轉換為8位數字

=TEXT(A23,"yyyymmdd")

說明：y、m、d指日期中的年月日，yyyy還可簡寫為e，關於這些內容，在text函數的教程中都有詳細解釋。

小結：本文總結了最基礎、常用的20個公式，關於日期時間類的問題其實還有很多，但是萬變不離其宗，掌握基礎公式之後，剩下的就是根據具體問題靈活應用了，如果在日期時間方面你還有其他問題歡迎留言交流。學習更多技巧，請收藏關注。


＊＊＊＊部落窩教育-excel時間日期的提取公式＊＊＊＊

原創：老菜鳥/部落窩教育（未經同意，請勿轉載）

更多教程：

相關焦點

  • excel函數公式應用:時間日期提取公式匯總,你用過哪些?
    如何快速在一組時間數據中分別提取出年月日、時分秒數據？如何快速計算某日期是年內第幾周、星期幾，以及日期之間間隔的天數、月數、年數、小時數、分鐘數？如何快速補全指定月份日期，合併日期和時間？今天老菜鳥針對上述在日常工作中經常會遇到的問題，總結了20個常用的關於日期和時間的公式，趕緊來看看吧！學習更多技巧，請收藏關注部落窩教育excel圖文教程。
  • excel函數公式大全利用TODAY函數YEAR函數MONTH函數自動提取年月
    excel函數公式大全之利用TODAY函數YEAR函數MONTH函數自動日期中的提取年月日。excel函數與公式在工作中使用非常的頻繁，會不會使用公式直接決定了我們的工作效率，今天我們來學習一下提高我們工作效率的函數TODAY函數YEAR函數MONTH函數。
  • EXCEL函數公式大全之利用MONTH函數和分類匯總匯總每個月的銷售額
    EXCEL函數公式大全之利用MONTH函數和分類匯總匯總每個月的銷售額。excel函數與公式在工作中使用非常的頻繁，會不會使用公式直接決定了我們的工作效率，今天我們來學習一下提高我們工作效率的函數MONTH函數與分類匯總的組合。
  • excel函數公式實戰：文本函數TEXT常用技巧匯總
    要說在excel中最特別的文本函數，那必定非TEXT函數莫屬，外界給它的稱號不計其數「文本之王」「整容大師」「千面鬼才」等等，由此可看出對它的喜愛。下面小花就和大家匯總了TEXT函數5種最實用的用法，趕緊來看看吧！學習更多技巧，請收藏。
  • excel函數公式:常用高頻公式應用總結(下)
    這個公式中涉及到兩個函數，首先來看MID函數，MID函數有三個參數，格式為：=MID(在哪提取,從第幾個字開始取,取幾個字)。MID(A2,7,8)表示從A2單元格的第七個數字開始截取八位，效果如圖所示：出生日期提取出來後卻不是我們需要的效果，這時候就該函數魔術師TEXT出馬了，TEXT函數只有兩個參數，格式為=TEXT(要處理的內容,「以什麼格式顯示」)，本例中要處理的內容就是MID函數這部分，顯示格式為"0-00-00"，當然你要用"0年00
  • EXCEL函數公式大全之利用WEEKNUM函數和SUMIF函數匯總每周銷售額
    EXCEL函數公式大全之利用WEEKNUM函數和SUMIF函數匯總每周手機銷售額。excel函數與公式在工作中使用非常的頻繁，會不會使用公式直接決定了我們的工作效率，今天我們來學習一下提高我們工作效率的函數WEEKNUM函數和SUMIF函數的組合。
  • excel函數公式大全利用if函數and函數sumif函數實現多重條件匯總
    excel函數公式大全利用if函數and函數sumif函數實現多重條件匯總，excel函數與公式在工作中使用非常的頻繁，會不會使用公式直接決定了我們的工作效率，今天我們來學習一下提高我們工作效率的函數if函數、and函數、sumif函數，利用這三種函數的組合實現對多重條件數值的匯總求和
  • excel函數公式大全之利用LARGE函數和SUM函數提取前五名銷售額
    excel函數公式大全之利用LARGE函數和SUM函數提取前五名銷售額，excel函數與公式在工作中使用非常的頻繁，會不會使用公式直接決定了我們的工作效率，今天我們來學習一下提高我們工作效率的函數LARGE函數和SUM函數。
  • excel函數公式：常用高頻公式應用總結（下）
    這個公式中涉及到兩個函數，首先來看MID函數，MID函數有三個參數，格式為：=MID(在哪提取,從第幾個字開始取,取幾個字)。,「以什麼格式顯示」)，本例中要處理的內容就是MID函數這部分，顯示格式為"0-00-00"，當然你要用"0年00月00日"這個格式顯示也沒問題，公式改為=TEXT(MID(A2,7,8),"0年00月00日")就可以了：
  • excel函數公式大全之利用DSUM函數實現複雜數據條件的匯總求和
    excel函數公式大全之利用DSUM函數實現複雜數據條件的匯總求和，excel函數與公式在工作中使用非常的頻繁，會不會使用公式直接決定了我們的工作效率，今天我們來學習一下提高我們工作效率的函數DSUM函數，利用這種函數實現複雜數據條件的匯總求和。
  • Excel120 | TEXT函數應用之一——格式化日期與時間
    問題來源前兩天，一位朋友發給我一張表，她要按月份匯總數據，要求從日期裡提取月份，表格如下：TEXT函數是一個超級好用的格式化文本函數，今天韓老師詳細給大家講來。篇幅有限，今天只講格式日期與時間，數字、貨幣等格式，下篇再講。
  • Excel函數公式教程:9個絕對用得上的excel日期公式,趕緊拿走!
    關於excel中的日期問題，是很多exceler都繞不開的話題，比如計算當月的工作日天數，當前日期是一年中的第幾天、第幾周等等，面對這些問題，估計不少小夥伴可能都會反應不過來，想著應該用啥函數，用啥公式，今天老菜鳥就為大家總結了9條職場人士最常用的計算日期的公式，以後在excel中遇到關於日期的問題，來翻翻這篇文章就行啦~ 使用Excel難免會遇到與日期有關的問題，今天分享一組實用公式
  • excel函數公式大全之利用sum函數進行匯總以及對多個匯總進行求和
    excel函數公式大全之利用sum函數進行匯總以及對多個匯總進行求和，excel函數與公式在工作中使用非常的頻繁，會不會使用公式直接決定了我們的工作效率，今天我們來學習一下提高我們工作效率的函數sum函數。
  • excel表格中的公式,你真的會用嗎?
    我發現很多同行都沒有用round公式保留小數，而是直接只顯示幾位小數，這樣做有什麼問題呢，最後匯總的數據可能不一致，導致花大量的時間去核對，沒必要，我們這樣把問題扼殺在搖籃裡面。如何使用round呢？DATEDIF(B2,C2,"d") （可直接複製哈），DATEDIF（開始日期，結束日期，計算單位（可以是年、月、日）），這樣工期就簡簡單單計算出來了，完全不用腦算時間，我的最愛。
  • 日期相關的Excel函數公式,你知道幾個?
    日期，與我們的工作、生活、學習等息息相關……在Excel中，日期也是非常重要的，但你知道怎樣快速的錄入日期，對日期進行計算嗎？一、返回當前日期。解讀：用公式=NOW()後，如果顯示的為日期，需要將單元格格式設置為【時間】哦！三、提取出生年月。
  • excel函數公式if怎麼用
    今天花點心思寫一下excel中if函數的用法，可能在數據小的時候大家用不到這些函數，但是如果表格數據量非常大的時候，那麼excel中if函數的節省時間的效果出出來了。那麼在C2中判定及格與不及格的if函數寫法是=IF(B2>=60,"及格","不及格")。我們先點擊C2單元格，然後在上方的公式輸入框中輸入我們的if函數，按回車鍵運行一下函數就可以了，然後通過C2單元格右下角的下拉複製功能，將函數應用到C2下方的其它單元格中就可以了。
  • EXCEL函數公式大全利用TODAY函數TEXT函數WEEKDAY函數製作備忘錄
    EXCEL函數公式大全利用TODAY函數TEXT函數WEEKDAY函數製作行程備忘錄。excel函數與公式在工作中使用非常的頻繁，會不會使用公式直接決定了我們的工作效率，今天我們來學習一下提高我們工作效率的函數TODAY函數、TEXT函數和WEEKDAY函數。
  • 新手學excel函數公式,必須從這幾個知識點學起!
    函數公式最excel基本的應用之一，但要想學好函數公式，必須先掌握以下幾個知識點。
  • Excel函數公式:含金量超高的9個常用日期函數公式
    日期函數在我們的日常生活中也是非常的常用的，如果能夠熟練掌握，將會對你的工作效率有很大的提高。一、Today函數：返回當前日期。方法：1、函數法：在目標單元格輸入公式：=TODAY()。2、快捷鍵法：Ctrl+；。二、Now函數：返回當前日期和時間。
  • excel函數公式大全之利用SUM函數IF函數的嵌套把成績劃為三個等級
    excel函數公式大全之利用SUM函數和IF函數的嵌套把學生成績劃為三個等級。excel函數與公式在工作中使用非常的頻繁，會不會使用公式直接決定了我們的工作效率，今天我們來學習一下提高我們工作效率的函數SUM函數和IF函數。