20個(gè)可分別提取年月日時(shí)分秒數(shù)據(jù)等的excel公式
?
作者:老菜鳥(niǎo)來(lái)源:部落窩教育發(fā)布時(shí)間:2020-06-18 14:07:11點(diǎn)擊:16312
編按:
哈嘍,大家好!如何快速在一組時(shí)間數(shù)據(jù)中分別提取出年月日、時(shí)分秒數(shù)據(jù)?如何快速計(jì)算某日期是年內(nèi)第幾周、星期幾,以及日期之間間隔的天數(shù)、月數(shù)、年數(shù)、小時(shí)數(shù)、分鐘數(shù)?如何快速補(bǔ)全指定月份日期,合并日期和時(shí)間?今天老菜鳥(niǎo)針對(duì)上述在日常工作中經(jīng)常會(huì)遇到的問(wèn)題,總結(jié)了20個(gè)常用的關(guān)于日期和時(shí)間的公式,趕緊來(lái)看看吧!
在實(shí)際工作中,經(jīng)常需要進(jìn)行日期和時(shí)間的計(jì)算,如:工作日天數(shù)、入職天數(shù)、合同到期日期、員工生日提醒、計(jì)算加班時(shí)間........
如果用人工計(jì)算會(huì)非常麻煩,而使用Excel函數(shù)公式則非常簡(jiǎn)單,今天給大家整理一期時(shí)間計(jì)算的公式套路大全,記得收藏起來(lái)慢慢看?。ū窘坛躺婕暗墓蕉急容^基礎(chǔ),不做過(guò)多講解,需要哪個(gè)公式直接套用即可。)
第一類公式:拆分類(共11個(gè))
如下圖所示,數(shù)據(jù)源為系統(tǒng)導(dǎo)出的格式,在這種數(shù)據(jù)源中,日期與時(shí)間是同時(shí)存在的,對(duì)于這種數(shù)據(jù)源來(lái)說(shuō),可以從中獲取對(duì)應(yīng)的日期、時(shí)間,進(jìn)而獲得年、月、日、時(shí)、分、秒以及年內(nèi)周數(shù),周內(nèi)天數(shù)以及星期幾等。
公式1:拆分日期
使用公式=INT(A2)得到日期,并修改單元格格式。
公式2:拆分時(shí)間
使用公式=MOD(A2,1)得到時(shí)間,并修改單元格格式。
公式3-5:獲取年月日
提取年的公式:=YEAR(A2)
提取月的公式:=MONTH(A2)
提取日的公式:=DAY(A2)
公式6-8:獲取時(shí)分秒
提取時(shí)的公式:=HOUR(A2)
提取分的公式:=MINUTE(A2)
提取秒的公式:=SECOND(A2)
公式9:年內(nèi)第幾周
公式=WEEKNUM(A2)可以得到一個(gè)數(shù)字,該數(shù)字表示日期在這一年的第幾周。
WEEKNUM的應(yīng)用場(chǎng)景:在某些場(chǎng)合,可能需要按周來(lái)進(jìn)行銷售分析,而如果數(shù)據(jù)中只有日期,此時(shí)就可以用WEEKNUM函數(shù)來(lái)輔助,再用透視表得到每周的匯總數(shù)據(jù),如下圖所示。
公式10-11:周內(nèi)第幾天和星期幾
先來(lái)看星期幾的公式:=TEXT(A2,"aaaa")
"aaaa"是TEXT函數(shù)中的星期代碼,關(guān)于TEXT函數(shù)之前有很多教程,不熟悉的小伙伴可以查看文章《如果函數(shù)有職業(yè),TEXT絕對(duì)是變裝女皇!》,這里就不贅述了。
需要說(shuō)明的是星期幾和周內(nèi)第幾天之間的區(qū)別。
通??梢允褂霉?span>=WEEKDAY(A2,2)得到數(shù)字所表示的星期幾,如圖所示。
然而這個(gè)公式的本質(zhì)卻并不是計(jì)算星期幾,第二參數(shù)2表示用星期一作為一周的第一天,按照這個(gè)規(guī)則來(lái)確定日期是本周的第幾天。
WEEKDAY的應(yīng)用場(chǎng)景:常見(jiàn)于對(duì)考勤統(tǒng)計(jì)中周末的判定,主要是星期六和星期天,公式=WEEKDAY(A2,2)>5可以直接得到判定結(jié)果,進(jìn)而作為具體統(tǒng)計(jì)的條件使用,也可以作為條件格式設(shè)置中的條件使用。
第二類公式:合并類(共2個(gè))
公式12:日期與時(shí)間合并
這個(gè)很簡(jiǎn)單,兩個(gè)單元格相加后再設(shè)置單元格格式就行了。
自定義格式的代碼為yyyy/m/d h:mm:ss
公式13:指定月份補(bǔ)全日期
這種問(wèn)題常見(jiàn)于考勤表中,指定月份就能得到該月的日期列表。
這類問(wèn)題通常使用DATE函數(shù)來(lái)補(bǔ)全日期,例如公式=DATE(2020,$A$2,COLUMN(A1))可以實(shí)現(xiàn)下圖所示的效果。
第三類公式:時(shí)間差和日期差(共5個(gè))
公式14-15:計(jì)算時(shí)間間隔小時(shí)數(shù)、分鐘數(shù)
要計(jì)算兩個(gè)時(shí)間之間的間隔小時(shí),可以使用公式:=(B2-A2)*24
要計(jì)算兩個(gè)時(shí)間之間的間隔分鐘,可以使用公式:=(B2-A2)*1440
公式16-18:計(jì)算兩個(gè)日期之間的天數(shù)、月數(shù)和年數(shù)
間隔天數(shù):=B9-A9
間隔月數(shù):=DATEDIF(A9,B9,"M")
間隔年數(shù):=DATEDIF(A9,B9,"Y")
間隔天數(shù)可以用兩個(gè)日期直接相減,間隔月數(shù)和間隔年數(shù)可以用DATEDIF函數(shù)得到,不熟悉這個(gè)函數(shù)的小伙伴可以查看這篇文章《用上DATEDIF,您永不再缺席那些重要的日子!》。
第四類公式:格式轉(zhuǎn)換類(共2個(gè))
格式轉(zhuǎn)換是指8位數(shù)字和日期之間的互換,常用于各類系統(tǒng)數(shù)據(jù)導(dǎo)出或?qū)霑r(shí)。
公式19:8位數(shù)字轉(zhuǎn)換為日期
=--TEXT(A16,"0-00-00")
說(shuō)明:TEXT前的--是為了將文本變成數(shù)值,如果不加--的話,得到的只是類似日期的一種文本結(jié)果。
公式20:日期轉(zhuǎn)換為8位數(shù)字
=TEXT(A23,"yyyymmdd")
說(shuō)明:y、m、d指日期中的年月日,yyyy還可簡(jiǎn)寫(xiě)為e,關(guān)于這些內(nèi)容,在text函數(shù)的教程中都有詳細(xì)解釋。
小結(jié):本文總結(jié)了最基礎(chǔ)、常用的20個(gè)公式,關(guān)于日期時(shí)間類的問(wèn)題其實(shí)還有很多,但是萬(wàn)變不離其宗,掌握基礎(chǔ)公式之后,剩下的就是根據(jù)具體問(wèn)題靈活應(yīng)用了,如果在日期時(shí)間方面你還有其他問(wèn)題歡迎留言交流。
本文配套的練習(xí)課件請(qǐng)加入QQ群:1043683754下載。
做Excel高手,快速提升工作效率,部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你!
掃下方二維碼關(guān)注公眾號(hào),可隨時(shí)隨地學(xué)習(xí)Excel:
相關(guān)推薦:
Excel中常用的日期公式《9條最實(shí)用的計(jì)算excel中關(guān)于日期的公式?。ńㄗh收藏)》
Excel中的日期函數(shù)(上)《玩轉(zhuǎn)excel日期函數(shù),一文看懂90%的日期運(yùn)算》
Excel中的日期函數(shù)(下)《玩轉(zhuǎn)excel日期函數(shù),一文看懂90%的日期運(yùn)算(下篇)》
Excel中輸入日期的方式《13種日期輸入法你只會(huì)第1種手動(dòng)輸入?》
最熱教程
- 像綠皮火車一樣長(zhǎng)像珠穆拉瑪峰一樣高的Excel表怎么操作才方便?
- Power Query實(shí)戰(zhàn):按指定次數(shù)遞增數(shù)據(jù)
- 2019年全網(wǎng)最全—excel提取身份證信息合集?。ńㄗh收藏)-下篇
- 明明沒(méi)有重復(fù),Excel卻判定數(shù)據(jù)重復(fù),這是怎么回事?
- 文本格式的求和,及求和中最容易出現(xiàn)的問(wèn)題解疑
- 致命缺陷:不懂一維表!
- 函數(shù)組合思維,你有嗎?
- 學(xué)會(huì)這2個(gè)公式,整理考勤數(shù)據(jù)只要一分鐘
- 就算被說(shuō)是拍馬屁也成,今天你應(yīng)該這樣發(fā)Excel報(bào)表……
- 如何計(jì)算Excel單元格中的算式,四種求和方法請(qǐng)收好!
最新教程
- 一個(gè)公式搞定空行的動(dòng)態(tài)插入
- 多功能銷售日歷模板制作
- 用正則函數(shù)快速拆分單元格中的同類數(shù)據(jù)
- 用免費(fèi)AI將圖片批量轉(zhuǎn)表格
- 先入先出出入庫(kù)模板中出庫(kù)批次的自動(dòng)匹配
- 用SUBSTITUTE替換法為產(chǎn)品找到最匹配的類別
- SHEETSNAME,一鍵獲得所有工作表名稱完成目錄制作
- 延遲退休落地,快速查詢你上幾年才退休!
- 快速將PDF電子發(fā)票數(shù)據(jù)提取到發(fā)票明細(xì)登記表的方法
- 提取、查找、替換數(shù)據(jù)的王者——REGEXP正則函數(shù)