【原】Excel120 | TEXT函數(shù)應(yīng)用之一——格式化日期與時(shí)間

bitterfleabane 2020-03-18

展開(kāi)全文

問(wèn)題來(lái)源

前兩天，一位朋友發(fā)給我一張表，她要按月份匯總數(shù)據(jù)，要求從日期里提取月份，表格如下：

韓老師不知道她要的月份日期是什么格式，所以給她了三種月份的提取方法，如下：

這三種格式對(duì)應(yīng)的公式如下：

我們看，不管是哪種格式，TEXT函數(shù)都能簡(jiǎn)單搞定。

TEXT函數(shù)是一個(gè)超級(jí)好用的格式化文本函數(shù)，今天韓老師詳細(xì)給大家講來(lái)。

篇幅有限，今天只講格式日期與時(shí)間，數(shù)字、貨幣等格式，下篇再講。

函數(shù)基礎(chǔ)

功能：

TEXT 函數(shù)可通過(guò)格式代碼對(duì)數(shù)字應(yīng)用格式，從而更改數(shù)字的顯示方式。

如果要按更可讀的格式顯示數(shù)字，或者將數(shù)字與文本或符號(hào)組合，它將非常有用。

語(yǔ)法

TEXT(數(shù)值,格式代碼)

Value：數(shù)值，或是計(jì)算結(jié)果為數(shù)字值的公式，也或?qū)Π瑪?shù)字值的單元格的引用。
Format_text：文本形式的數(shù)字格式。

　　text返回的一律都是文本形式的數(shù)據(jù)。如果需要計(jì)算，可以先將文本轉(zhuǎn)換為數(shù)值，然后再計(jì)算。

文本型數(shù)值遇到四則運(yùn)算會(huì)自動(dòng)轉(zhuǎn)為數(shù)值。

但文本會(huì)不參與sum之類(lèi)的函數(shù)運(yùn)算。

分類(lèi)應(yīng)用

TEXT 函數(shù)主要是通過(guò)格式代碼來(lái)應(yīng)用格式的。

今天，韓老師來(lái)講TEXT函數(shù)格式日期與時(shí)間的用法：

格式日期

先看下圖，TEXT函數(shù)通過(guò)不同的格式代碼，轉(zhuǎn)換日期格式的結(jié)果：

（格式代碼，即是TEXT公式的第二個(gè)參數(shù)，下圖公式中“”內(nèi)的部分）

公式中的代碼及其含義：

代碼	含義
m	將月顯示為不帶前導(dǎo)零的數(shù)字。
mm	根據(jù)需要將月顯示為帶前導(dǎo)零的數(shù)字。
mmm	將月顯示為縮寫(xiě)形式（Jan 到 Dec）。
mmmm	將月顯示為完整名稱(chēng)（January 到 December）。
d	將日顯示為不帶前導(dǎo)零的數(shù)字。
dd	根據(jù)需要將日顯示為帶前導(dǎo)零的數(shù)字。
ddd	將日顯示為縮寫(xiě)形式（Sun 到 Sat）。
dddd	將日顯示為完整名稱(chēng)（Sunday 到 Saturday）。
yy	將年顯示為兩位數(shù)字。
yyyy	將年顯示為四位數(shù)字。

另：阿拉伯?dāng)?shù)字與中文數(shù)字轉(zhuǎn)換時(shí)：

格式參數(shù)為"[dbnum1]"：普通的大寫(xiě)，如“七百八十九”；
格式參數(shù)為"[dbnum2]"：財(cái)務(wù)專(zhuān)用大寫(xiě)，如“柒佰捌拾玖”；
格式參數(shù)為"[dbnum3]"：阿拉伯?dāng)?shù)字之間加單位，如“７百８十９”；但用"[dbnum3]"轉(zhuǎn)成的數(shù)字是全角，所與如果轉(zhuǎn)換成普通的半角，TEXT函數(shù)之外要套用ASC函數(shù)。

格式時(shí)間

公式中的代碼及其含義：

代碼	含義
h	將小時(shí)顯示為不帶前導(dǎo)零的數(shù)字。
[h]	以小時(shí)為單位顯示經(jīng)過(guò)的時(shí)間。如果使用了公式，該公式返回小時(shí)數(shù)超過(guò) 24 的時(shí)間，請(qǐng)使用類(lèi)似于 [h]:mm:ss 的數(shù)字格式。
hh	根據(jù)需要將小時(shí)顯示為帶前導(dǎo)零的數(shù)字。如果格式含有 AM 或 PM，則基于 12 小時(shí)制顯示小時(shí)；否則，基于 24 小時(shí)制顯示小時(shí)。
m	將分鐘顯示為不帶前導(dǎo)零的數(shù)字。
m	注釋 m 或 mm 代碼必須緊跟在 h 或 hh 代碼之后或緊跟在 ss 代碼之前；否則，Excel 會(huì)顯示月份而不是分鐘。
[m]	以分鐘為單位顯示經(jīng)過(guò)的時(shí)間。如果所用的公式返回的分鐘數(shù)超過(guò) 60，請(qǐng)使用類(lèi)似于 [mm]:ss 的數(shù)字格式。
mm	根據(jù)需要將分鐘顯示為帶前導(dǎo)零的數(shù)字。
mm	注釋 m 或 mm 代碼必須緊跟在 h 或 hh 代碼之后或緊跟在 ss 代碼之前；否則，Excel 會(huì)顯示月份而不是分鐘。
s	將秒顯示為不帶前導(dǎo)零的數(shù)字。
[s]	以秒為單位顯示經(jīng)過(guò)的時(shí)間。如果所用的公式返回的秒數(shù)超過(guò) 60，請(qǐng)使用類(lèi)似于 [ss] 的數(shù)字格式。
ss	根據(jù)需要將秒顯示為帶前導(dǎo)零的數(shù)字。如果要顯示秒的小數(shù)部分，請(qǐng)使用類(lèi)似于 h:mm:ss.00 的數(shù)字格式。
AM/PM、am/pm、A/P、a/p	基于 12 小時(shí)制顯示小時(shí)。時(shí)間介于午夜和中午之間時(shí)，Excel 會(huì)使用 AM、am、A 或 a 表示時(shí)間；時(shí)間介于中午和午夜之間時(shí)，Excel 會(huì)使用 PM、pm、P 或 p 表示時(shí)間。