Excel | 8位數日期20170530轉標準日期格式

2021-02-23 又一個實用Excel技巧

工作中經常遇到存儲為8位數字的日期

我自己在做文件夾命名時

也喜歡這樣寫日期

 

but如果在Excel中遇到這種格式

處理起來卻有點不方便

畢竟它不是標準的日期格式

 

比如在日期加減時

Excel無法自動判斷

一個月結束後進入下一個月

real尷尬……

 

當然也沒法直接用weekday等針對日期的函數

提取年月日也不能用year、month、day

而是需要當做文本

用left、mid、right函數處理

更不用說透視表的日期分組功能等等

 

所以為了處理方便

建議大家增加一個步驟

把8位數的日期

先轉化為標準日期格式！

 

怎麼做呢？

我有2個方法

 

 

方法1 - 提取年月日然後組合成日期

 

還記得date函數嗎？

不記得看這裡：

Excel | 日期的拆分和重組

 

不過因為8位數日期實際上不是日期

就不能用年月日函數提取年月日

而是要用左中右函數

=LEFT(單元格,左起位數)

=MID(單元格,起始位置,位數)

=RIGHT(單元格,右起位數)

 

再用date函數組合出日期：

=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))

 

敲好記的哦！

 

 

方法2 - 文本函數法

 

還記得text函數嗎？

簡直是超級好用的萬金油函數呢

=TEXT(單元格,格式代碼)

 

它有一個格式代碼

是可以把一串數字用一個符號拆開

比如把8位數字的年月日拆開的公式是：

=TEXT(A1,"0!/00!/00")

 

感嘆號!後面的符號

就是用來做拆分的分隔符的

0代表數字，幾個0就是幾位

保留位數的優先級是從右往左的

 

上面公式的意義就是：

在最後兩位前面加一個分隔符/

然後再取兩位，再在前面加一個分隔符

這樣就拆開了年月日

 

還不太理解的話可以看下面幾個示例

（第4、5行分割出來的就不是日期了）

 

有點明白了吧

來驗證一下

對剛剛分割好的日期+1

然後更改為「短日期」格式

 

不需要擔心分割出的結果是文本

聰明的Excel可以自己識別出日期呢

比如上面+1的示例就說明了這點

 

只是默認的結果不能修改

日期對應的顯示格式

強迫症如果要特別特別標準的日期

就在上面公式的基礎上*1

或者+0

就得到了特別特別標準的日期

 

 

你更喜歡哪個方法？

留言告訴我吧！

 

 

細蓉~

相關焦點

  • 關於Excel標準日期格式,你真的了解嗎?
    先告訴大家，前三種是標準日期格式，後三種不是那麼，Excel的標準日期格式有哪些好處呢？答案是，可以參與運算可以參與什麼運算呢？ 主要包括：加減法、比大小、函數、文本格式識別等等。舉個例子：分析多能工上門時長，首先從運營平臺導出師傅的上門時間及完成時間，因為數據已經使用了Excel的標準日期格式，咱們可以直接對兩個日期（時間）做減法，即可直接得到答案。
  • Excel日期格式轉換方法大全,關於日期函數
    我們經常會匯總一些同事表格，最終全部完善到一個表格中，不過這些表格中的日期格式各有差異。
  • 9個Excel日期小常識
    打開自定義單元格格式，將這種特殊格式的格式刪除掉。將年月和日期合併起來，並轉換成標準日期。=--SUBSTITUTE(A2&"."&B2,".","-")用.作為分隔符號的並不是標準日期，需要再替換成以-作為分隔符號，再藉助--轉換成數值，最後設置單元格格式為日期格式。7.將庫存日期店鋪合併起來，直接用&連接起來出錯。
  • 一系列工作中Excel轉換日期格式的函數,夠用了!
    用Excel來記錄日期後發現格式不對，需要對前面的大量輸入過的日期格式進行修改，如果數據較少，可以手動修改一下，如果數據過多的話，
  • [智能]Excel日期到期自動提醒功能(附神器)
    朋友：excel裡邊，有一欄是日期，是未來的日期，然後能不能設置到如果表格上的日期是當天的日期，會有提醒功能的呢？我說明白了嗎？盧子：可以做到。你的就算說不明白，我也得想明白選擇區域，設置條件格式，選擇新建規則。
  • excel日期轉星期的六種方法,你會幾種哦!
    excel日期轉星期，可以使用相關的日期轉星期函數公式，也可以使用自定義格式來操作。　　下面我們分別列舉多種方法。
  • Excel日期格式轉換大全
    課程中有學員提到工作中經常需要從不同部門收集數據表格，其中的日期數據常常出現各種不規範的問題，要如何才能將這些不規範的日期格式轉換成規範的呢？只有轉換成日期格式，才可以做計算、利用日期函數提取數據，以及利用數據透視表進行日期組合操作。這篇文章曾經推送過，今天我們再發一次，一起來回顧一下這個問題的解決方案。
  • 從頭細說,Excel四大類日期設置的注意點
    標準的日期其實是數字格式，可以通過將單元格設置為短日期或者長日期格式。
  • Excel中將「錯誤日期」轉換為標準格式的6個應用技巧解讀
    ，多人協作的Excel表格，填寫的日期可以說是「五花八門」，如果這些「日期」要參與運算或統計，在處理上非常的麻煩……那將「五花八門」的日期整理整理成標準日期就是辦公必備的一項技能了。一、Excel日期轉換：常規類型的「日期」。目的：將常規類型的「日期」轉換為標準格式。
  • 批量修改日期格式
    在一些手工錄入的表格裡，會因為輸入不規範導致出現各種各樣的日期格式，如下圖：在這種狀態下，進行日期的篩選統計
  • Excel日期和時間的錄入
    在excel表格中錄入日期和時間之前，要弄清楚excel表格對他們的定義。
  • EXCEL中日期轉換160831變為2016-08-31方法
    眾所周知，excel有日期格式，但是我們手裡的數據很多時候都是非標準日期格式，而是數字序列比如20160831 ,160831 ,16.08.31
  • EXCEL日期輸入方法,告訴你怎麼規範的偷懶
    本期分享處理「假日期」的方法（點擊此處獲取海量辦公素材先收禮再閱讀）Excle中日期、時間的格式多樣。今天跟大家分享一下怎麼處理「假日期」，再幫大家梳理一下日期格式的定義的問題。我們的原則就是」在規範的前提下偷懶「！有人習慣用數字串代替日期，輸入excel中。肯定是為了省勁兒！例如把2016年7月4日輸成20160704，但實際並非日期格式。
  • 教你輕鬆完成Excel中數值到日期的華麗轉身
    在Excel2013中，每個單元格內數據都可以有多種格式，比如文本、數字和日期等等。
  • 將日期轉換為中文短日期格式
    點擊上方藍字關注 Excel函數與公式關注後發送函數名稱，即可獲取對應教程原創作者 | 李銳微信公眾號 | Excel函數與公式（ID：ExcelLiRui）微信個人號 | （ID：ExcelLiRui520）將日期轉換為中文短日期格式
  • 【Excel日期數轉星期數的技巧】
    所以，今天我們介紹幾個給日期填充星期幾的小技巧，感興趣的朋友可以繼續看下去。今天我們介紹的第一種方法是：通過設置單元格格式，直接把日期轉化為星期幾。具體操作步驟是：選中日期，單擊滑鼠右鍵「設置單元格格式」（快捷鍵：Ctrl+1），在「數字」的下拉菜單中選中「日期」，滑動右側垂直滾動條，選擇自己需要的星期格式，單擊確定就可以得到我們需要的星期格式。
  • Excel中批量轉換日期格式的方法
    我是花花，幾天不見甚是想念，從上一篇發表了SUMIF函數使用案例後有許多同學在問裡面的TOODAY()函數是什麼意思【意思是返回日期格式的當前日期】，沒錯，今天給大家帶來的教程是怎麼在Excel中批量轉換日期格式的方法，下面進入正題：　　操作步驟如下： 　　1.打開Excel工作表，在工作表中輸入數據並選擇這些數據。
  • Excel表格日期格式轉換全攻略
    在Excel中我們常常會輸入各種日期，但有時候為了節省時間我們可能輸入的不是那么正規，這時候就可以設置一下Excel中的日期格式自動轉換功能，即可輕鬆轉換成自己想要的格式了
  • Excel自動提取出生日期,就這篇教程足夠了!
    excel計算出生日期這個問題，HR，財務都用得比較多。
  • 【新手必讀】Excel日期格式與函數,看完你就懂了
    3設定Excel日期格式的顯示■ 常見格式設定>因各國家地區習慣不同，Excel支持多種日期格式的顯示。可在單元格上右鍵>設置單元格格式(快捷鍵CTRL+1)中，定義日期的顯示格式。■ 自定義日期格式（新手可跳過）若以上格式不能滿足你的需要，也可以自己定義日期格式的顯示。