Excel利用TEXT函數設置日期、時間格式並將文本日期轉換為真正的日期

2021-03-02 效率辦公365

Q：上周五有朋友在公眾號中問，知道身份證號如何計算年齡？

A：要解決這個問題，我們還需要介紹兩個函數：TEXT和DATEDIF，今天我們先來介紹TEXT函數。

TEXT函數用來設置【數字】格式，類似於右鍵【設置單元格格式】中【數字】選項卡的功能，今天我們先來介紹設置日期、時間格式。

TEXT函數的語法為：

1.年year

格式代碼yyyy或小寫e，用來顯示年份的完整四位；

2.月month

格式代碼m，顯示月份，不帶前導0；

格式代碼mm，顯示月份，帶前導0；

格式代碼mmm，顯示月份的英文縮寫；

格式代碼mmmm，顯示月份的英文全拼；

格式代碼mmmmm，顯示月份的英文首字母；

示例如下圖。

3.日day

格式代碼d，顯示日，不帶前導0；

格式代碼dd，顯示日，帶前導0；

示例如下圖。

4.星期

格式代碼aaa，顯示星期的中文簡稱；

格式代碼aaaa，顯示星期的中文全稱；

格式代碼ddd，顯示星期的英文縮寫；

格式代碼dddd，顯示星期的英文全拼；

示例如下圖。

5.時hour

格式代碼h，顯示小時，不帶前導0；

格式代碼hh，顯示小時，帶前導0；

示例如下圖。

6.分minute

m既是月份的格式代碼，又是分鐘的格式代碼，單獨使用時默認為月份，當需要表示分鐘時，需要和其他格式代碼合用。

格式代碼h:m，顯示分，不帶前導0；

格式代碼hh:mm，顯示分，帶前導0；

示例如下圖。

7.秒second

格式代碼s，顯示秒，不帶前導0；

格式代碼ss，顯示秒，帶前導0；

示例如下圖。

8.上午/下午

格式代碼am/pm，顯示上下午的英文縮寫；

格式代碼A/P，顯示上下午的英文簡寫（大寫）；

格式代碼a/p，顯示上下午的英文簡寫（小寫）；

格式代碼上午/下午，顯示上下午的中文；

示例如下圖。

9.綜合運用示例

將下列日期時間分別改成中文和英文的格式，示例如下圖。

一般在輸入日期時，使用「-」或「/」號作為年、月、日的間隔符，系統會自動將其識別為日期，如果省略間隔符直接輸入數字串，比如20200308，則系統不會識別其為日期，從身份證號碼截取的出生日期也是如此，需要使用TEXT將其轉換為日期。

需要注意的是，轉換後雖然是日期形式，也能參與加減運算，但是其數據類型依然是文本，並不是日期，因此無法通過右鍵【設置單元格格式】的方法設置格式，如需把數據類型改成真正的日期，可以通過減負運算--、加零+0或減零-0，示例如下圖。

相關焦點

  • EXCEL函數公式大全之利用TEXT函數將日期文本轉換成標準日期格式
    EXCEL函數公式大全之利用TEXT函數將日期文本轉換成標準日期格式。excel函數與公式在工作中使用非常的頻繁，會不會使用公式直接決定了我們的工作效率，今天我們來學習一下提高我們工作效率的函數TEXT函數。在日常工作中往往由於我們的粗心大意，把日期格式數據製作成文本格式。
  • Excel自動生成日期及格式設置、禁止輸入3-1轉日期、日期轉換文本
    在 Excel中，默認情況下，用拖拉的辦法不能自動生成日期，需要先寫一個函數再拖拉才能自動生成。除這種方法外，還可以用「序列」的辦法自動生成，並且一次可以生成一年甚至更長時間的日期。日期格式有許多種，有數字加短橫線的，也有數字加漢字的，還可以自定義。
  • Excel表格怎麼提取身份證號碼的出生日期並快速轉換為日期格式
    因此這篇文章將介紹用公式提取的方式，並用公式將提取的日期轉換為可直接運算的日期格式。    雖然提取出了身份證號碼的出生日期，但它並不是日期格式，不能直接進行日期相關的運算，因此需要轉換為日期格式。
  • excel通過分列將文本轉換為日期,並運用datedif函數計算相隔時間
    >today()函數 當前日期now()函數 當前日期的當前時間下面給大家介紹一下通過分列轉換日期格式以及怎樣根據日期計算相隔的天數。關於分列：當我們通過設置單元格格式把文本格式改為日期格式時，就會出現錯誤，因為單元格格式只能改變顯示的方式，而不能改變實際的格式，這種情況就可以運用分列功能。
  • 如何通過Excel函數將日期轉換為正確格式？
    在一個excel函數中，我們提取出來的日期是數字，如何轉化為正確的日期格式。1.首先我們將通過快速填充提取出一些日期來，下面進行轉換為正確的日期格式。2.插入空白列之後，輸入輸入函數text。4.逗號隔開之後輸入正常的日期格式加雙引號。
  • EXCEL表格中把日期格式轉為文本格式
    有時候，我們需要在excel表格中把日期格式改成文本，如下圖所示;如果我們直接點文本，會把日期變成一串我們無法直接使用的數值，如下圖第一，使用excel中的TEXT函數，該函數格式如下TEXE(VALUE,FORMAT_TEXE),VALUE為我們需要轉換的數值，FORMAT_TEXT為轉換的文本格式，如下圖所示：
  • Excel120 | TEXT函數應用之一——格式化日期與時間
    TEXT函數是一個超級好用的格式化文本函數，今天韓老師詳細給大家講來。篇幅有限，今天只講格式日期與時間，數字、貨幣等格式，下篇再講。如果要按更可讀的格式顯示數字，或者將數字與文本或符號組合，它將非常有用。語法TEXT(數值,格式代碼)Value：數值，或是計算結果為數字值的公式，也或對包含數字值的單元格的引用。
  • Excel日期及時間函數——DATEVALUE函數
    DATEVALUE函數概念及語法DATEVALUE函數概念：將日期值從字符串轉化為序列數，表示日期時間代碼的日期；DATEVALUE 函數將存儲為文本的日期轉換為 Excel 識別為日期的序列號。 例如，公式=DATEVALUE("1/1/2008") 返回 39448，即日期 2008-1-1 的序列號。
  • EXCEL日期和時間函數的使用方法
    ▼表 1-3 文本與日期、時間格式間的轉換函數名稱 功能DATEVALUE 將文本格式的日期轉換為序列號TIMEVALUE 將指定日期的序列號轉換為文本日期▼表 1-4 其他日期函數DATEVALUE返回當前日期是星期幾特定日期的序列號函數格式： DATEVALUE (date_text)參數說明： date_text：表示要轉換為編號方式顯示的日期的文本字符
  • Excel日期格式轉換大全
    課程中有學員提到工作中經常需要從不同部門收集數據表格，其中的日期數據常常出現各種不規範的問題，要如何才能將這些不規範的日期格式轉換成規範的呢？只有轉換成日期格式，才可以做計算、利用日期函數提取數據，以及利用數據透視表進行日期組合操作。這篇文章曾經推送過，今天我們再發一次，一起來回顧一下這個問題的解決方案。
  • Excel如何使用函數將日期轉變為星期？
    下面小編來教大家如何使用excel的函數將日期轉變為對應的星期，提高工作效率。1.首先我們可以找到一列日期，要將日期找到對應的星期。4.後面輸入四個a，一定要加上雙引號，代表的是文本格式。使用函數將日期轉換為對應的星期，不僅是可以轉化為中文，也可以轉換成英文，下面看如何轉換為英文的星期吧。
  • excel錄入技巧：如何進行日期格式的轉換
    相信大多數人在剛接觸excel時，在excel中錄入日期的格式都是類似於「xxxx.xx.xx」的形式，以「.」作為年月日的分隔。但是這樣的日期卻不符合excel日期的填寫規範，是錯誤的日期。今天我們就來認識下excel中日期的填寫規範以及怎樣將錯誤的日期格式改為正確的日期格式，趕緊來看看吧！
  • excel錄入技巧:如何進行日期格式的轉換
    相信大多數人在剛接觸excel時，在excel中錄入日期的格式都是類似於「xxxx.xx.xx」的形式，以「.」作為年月日的分隔。但是這樣的日期卻不符合excel日期的填寫規範，是錯誤的日期。今天我們就來認識下excel中日期的填寫規範以及怎樣將錯誤的日期格式改為正確的日期格式，趕緊來看看吧！
  • excel日期格式轉換秘籍,必須收藏的4種斜線日期格式轉換心法
    在excel中，我們一般將日期格式分為五種，第一種：斜線日期（2020/1/25）;第二種：橫線日期（2020-1-25）;第三種：無分隔線日期（20200125）；第四種：文字日期（2020年1月25日），第五種：點分隔日期（2020.1.25），下面介紹斜線日期轉換成其他四種日期的方法
  • excel日期格式轉換秘籍:必須收藏的4種無分隔線日期格式轉換心法
    在日常工作中，有時候我們需要根據要求對各種日期進行轉換，由於各種日期轉換方法不一樣，所以下面整理出用函數將無分隔線格式日期轉換成其他日期格式的方法。1、無分隔線日期轉換為斜線日期的方法。如圖所示，輸入公式：TEXT(B3,"0!/00!/00"),然後按Enter，向下填充即可。
  • 文本與日期、時間格式之間的轉換技巧,簡單易懂,辦公必備
    Excel的數據格式分為常規、數字、貨幣、短日期、長日期、時間、百分比、分數、文本等多種類型，各種類型之間基本上都是可以相互轉換的，但必須掌握一定的技巧，例如文本與日期、時間格式之間的轉換，必須要用到Datevalue等函數。
  • 日期轉換
    我們會遇到各種各樣格式的時間表達方法，把這些表達轉化成有效地系統可識別的時間就很有必要獲取當前時間/日期這是最基本的一個需求。先把系統時間列印出來。>2017/7/13 0:35:52 2017/7/13 0:35:52沒錯，A哥的確是在深夜寫的這篇文章生成時間很多情況下，我們得到的時間格式並不是標準的內部自帶格式，因此需要轉換成內部時間格式。
  • 日期格式變文本格式
    到這裡，估計小夥伴們知道了，我導入失敗的原因就是 看上去同一個模樣的日期其實格式是不一樣的。找到失敗的原因，剩下的就是怎樣解決這個問題了。「把單元格格式設置成文本格式不就行了」，No，No，No，還是太天真，不信你試試，是不是發現「2021/1/18」變成了「44214」。
  • excel日期時間函數
    注意:Excel按順序的序列號保存日期，這樣就可以對其進行計算。如果工作簿使用的是1900日期系統，則Excel會將1900年1月1日保存為序列號1。同理，會將1998年1月1日保存為序列號35796，因為該日期距離1900年1月1日為35795天。 　　實例：如果採用1900日期系統(Excel默認)，則公式「=DATE(2001，1，1)」返回36892。
  • Excel如何使用text函數提取日期和時間？
    excel中如何使用text函數來提取日期和時間？下面小編就來教給大家通過實例來演示一下操作方法吧。1.首先我們打開excel表格要將下方的日期和時間進行提取。2.提取日期的話是輸入test函數。3.輸入單元格之後使用年月日的格式，以年月日英文首字母表示。