金山WPS表格中輕鬆統一日期格式

來源:互聯網
上載者:User

   平常辦公中經常需要收集匯總下級各部門上報的資料表格。由於各部門輸入人員並沒有按統一標準格式輸入日期,摘要資料之後往往會發現摘要資料中的日期有很多種格式。除了2009-2-3、1978年5月6日等標準格式的日期外,最常見的還有文字格式設定的日期78.2.3、2009.2.25和數字格式的日期20090506、091223兩類(圖1)。

  圖1

  在Excel中文本、數字格式的日期即無法統一設定日期格式也不能用公式進行日期計算。對於記錄少的小表格我們可以重新輸入日期,但當表格中有幾百上千條記錄時,重新輸入日期顯然並不容易。其實在WPS表格中通過函數就可以輕鬆把文本、數位日期轉換成標準日期,這樣要統一設定日期格就很簡單了吧?下面就針對圖示表格中D列的日期,為大家介紹一下轉換方法。

  1.分類

  先選中D2儲存格,單擊工具列的“升序排列”,即可讓所有日期按文本日期、數值日期、標準日期的順序分三類分別集中在一起。然後右擊D列的列標選擇“插入”,在其後插入一列(E列),並選中E列右擊選擇“設定儲存格格式”按需要設定適當的日期格式(圖2)。再來就可分類對日期進行轉換了,標準日期就不用處理了,只要對文本和數值兩類資料進行轉換即可。

  圖2

  2.轉換09.2.3

  對於排在最前面的78.2.3、2009.1.25等文本日期,只要在E2輸入公式=VALUE(SUBSTITUTE(D2,".","-")),然後選中E2儲存格拖動其右下角的黑色小方塊(填滿控點)把公式向下複製填充,即可把D列的日期轉換成標準日期顯示在E列(圖3)。函數公式表示把D2內容的.替換成-,再轉換成數值。在此,若只用SUBSTITUTE函數替換成日期格式,雖然顯示的是日期但Excel是把它當做文本處理的,所以必需再用VALUE轉成數值才會被Excel當成日期處理。

  圖3

  3.轉換20090203

  對於20090201、980201這樣的數值日期格式也很簡單,只要在第一個數值日期的E列儲存格(E11)輸入公式=VALUE(TEXT(D11,"##00-00-00")),然後選中E11儲存格同樣拖動其填滿控點把公式向下複製填充,即可把D列的日期轉換成標準日期顯示在E列(圖4)。函數公式表示把D2的數字內容按##00-00-00格式轉成2009-02-01、98-02-01這樣的文本,再用VALUE把文本轉換成數值以讓Excel識別為日期。

  圖4

  OK,現在只要把轉換後的E列日期(本例E2:E17)選中進行複製,再右擊D2儲存格選擇“選擇性粘貼”,以“數值”格式進行粘貼。然後選中D列,右擊選擇“設定儲存格格式”,按需要統一設定一種日期格式即可。

  此外,若配合使用IF函數把兩個公式集合在一起,操作上還可以更簡單些。只要右擊D列的列標選擇“插入”,在其後插入一列(E列),並選中E列右擊選擇“設定儲存格格式”按需要設定好日期格式。在E2儲存格輸入公式=IF(ISERROR(VALUE(TEXT(D2,"##00-00-00"))),IF(ISERROR(VALUE(SUBSTITUTE(D2,".","-"))),D2,VALUE(SUBSTITUTE(D2,".","-"))),VALUE(TEXT(D2,"##00-00-00"))),並雙擊其右下角的填滿控點向下填充。即可把D列的日期轉換成統一的標準日期顯示在E列。然後右擊D列列標選擇“隱藏”以隱藏D列,直接用E列做為“出生年月”參與計算即可。不過這樣公式太長輸入不便又容易輸錯,若只是想統一日期格式相信大家會更喜歡前面的方法吧。這一大串公式最大的用處是可以把你在D列輸入的各種日期即時轉換成標準日期。

  註:對於兩位元的年度,Excel只能自動識別為1930-2029期間的年份,如果你用兩位年輸入的不是這期間的年份那最終還是得動手直接修改。

相關文章

Beyond APAC's No.1 Cloud

19.6% IaaS Market Share in Asia Pacific - Gartner IT Service report, 2018

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。