跳到主要內容

如何在Excel中將日期轉換為數字字符串或文本格式?

在本教程中,我將介紹幾種解決有關在Excel中將日期轉換為數字或文本格式的不同情況的方法。
doc日期至數字文字1


1.將日期轉換為文本

本節提供了在Excel中將日期轉換為文本的方法。

要將日期轉換為文本,您只需要一個公式。

公式:= TEXT(date,“ date_format”)

參考: 日期:您要轉換為文本的日期單元格
日期格式: 將日期轉換為文本後要顯示的格式

在以下情況下,我將告訴您如何使用此公式。

1.1將日期轉換為mm / dd / yyyy格式的文本

例如,將單元格A3中的日期轉換為mm / dd / yyyy,使用公式
= TEXT(A3,“ mm / dd / yyyy”)
Enter 關鍵
doc日期至數字文字2

如果要將單元格A8中的日期轉換為dd / mm / yyyy,請使用以下公式
= TEXT(A8,“ dd / mm / yyyy”)
Enter 關鍵
doc日期至數字文字3

1.2將日期轉換為mmddyyyy或ddmmyyyyy格式的文本

如果要將單元格A13中的日期轉換為文本,但格式為mmddyyyy,請鍵入以下公式
= TEXT(A13,“ mmddyyyy”)
Enter 關鍵
doc日期至數字文字4

如果要將單元格A18中的日期轉換為ddmmyyyy格式的文本,請鍵入以下公式
= TEXT(A18,“ ddmmyyyy”)
Enter 關鍵
doc日期至數字文字5

1.3將日期轉換為其他格式的文本

實際上,無論您要將日期轉換為哪種文本格式,都只需在公式中的引號之間鍵入所需的格式。

日期(A3) 12/23/2019
公式 = TEXT(A3,“ mm-dd-yyyy”) = TEXT(A3,“ mm / dd”) = TEXT(A3,“ dd”) = TEXT(A3,“ d / m / y”)
結果 23-12-2019 23/12 23 23/12/19

全新!
約會工具

16種新的日期功能為您解決了90%的Excel中日期處理任務。

◆加上或減去迄今為止的年/月/日/週, 點擊查看更多細節.

◆加上或減去秒/分鐘/小時 點擊更多.

◆計算兩個日期之間的周末/工作日。 點擊更多.

◆從日期中刪除時間。 點擊查看更多細節.

◆將時間轉換為十進制秒/單位/小時。 點擊查看更多細節.

◆將多種日期格式轉換為美國標準日期格式。 點擊查看更多細節.

30天免費試用,具有完整功能,30天無理由退款。


2.將日期轉換為數字

在本節中,我提供了將日期轉換為mmddyyyy格式的5位數字的方法。

2.1將日期轉換為5位數字的數字

如果要將日期轉換為5位數格式的數字,請按照以下步驟操作:

1.右鍵單擊包含要轉換為數字的日期的單元格,然後在右鍵菜單中選擇 單元格格式 常見。
doc日期至數字文字6

2。 在 單元格格式 對話下 聯繫電話 標籤,選擇 從的窗格 類別.
doc日期至數字文字7

3。 點擊 OK。 所選單元格中的日期已轉換為mmddyyyy格式的數字字符串。
doc日期至數字文字8

2.2將日期轉換為mmddyyyy或ddmmyyyyy格式的數字

如果要將日期轉換為mmddyyyy或ddmmyyyyy格式的數字字符串,則還可以應用“設置單元格格式”功能。

1.右鍵單擊包含要轉換為數字的日期的單元格,然後在右鍵菜單中選擇 單元格格式 常見。

2。 在裡面 單元格格式 對話框下 聯繫電話 標籤,選擇 習俗 來自 類別 窗格,然後轉到右側部分,輸入 MMDDYYYY類別 文本框。
doc日期至數字文字9

3。 點擊 OK。 所選單元格中的日期已轉換為mmddyyyy格式的數字字符串
doc日期至數字文字10

如果要將日期轉換為其他格式的數字字符串,可以參考以下列表。

日期 2/23/2019
將單元格設置為自定義格式 dmmyyyy 天啊 y 年年月日
顯示屏 23022019 230219 022019 20190223

3.單擊將日期轉換為月/日/年或其他日期格式

如果您想將日期快速轉換為月,日,年或其他日期格式,則 套用日期格式 的效用 Excel的Kutools 將是一個很好的選擇。

告別鼠標手和頸椎病

300個高級工具 Kutools for Excel 解決 80% 數秒內完成 Excel 任務,讓您擺脫數千次鼠標點擊。

輕鬆應對1500個工作場景,無需浪費時間尋找解決方案,有更多時間享受生活。

每天為 80+ 高效能人士提高 110000% 的生產力,當然也包括你。

不再被痛苦的公式和VBA折磨,給你的大腦一個休息和快樂的工作心情。

30 天免費試用全功能,30 天無理由退款。

更好的身體創造更好的生活。

免費安裝Kutools for Excel,請執行以下操作:

1.選擇包含要轉換為其他日期格式的日期的單元格。

2。 點擊 庫工具 > 格式 > 套用日期格式.
doc日期至數字文字11

3。 在裡面 套用日期格式 對話框中,選擇要在其中使用的日期格式 日期格式 窗格,與此同時,轉換後的結果將在右側預覽 預覽 窗格。
doc日期至數字文字12

4。 點擊 Ok,則您選擇的日期已轉換為您選擇的日期格式。
doc日期至數字文字13

點擊即可將日期轉換為多種個性化日期格式

應用日期格式

有關日期轉換的更多提示


下載樣本文件

樣品


推薦的生產力工具

Office選項卡-Microsoft Office 2019-2003和Office 365中的選項卡式瀏覽,編輯,文檔管理


辦公室標籤

Kutools for Excel-為Microsoft Excel組合了300多種高級功能和工具


kutools選項卡
kutoolsp選項卡
Comments (40)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Hi, I would very much appreciate your help with this formula.How can I switch month, date ,year “030624”in to a number 306042 and put the number in sequential order after the number 306042 like: 3060421
This comment was minimized by the moderator on the site
Hi, I would very much appreciate your help with this formula.How can I switch month, date ,year “030624”in to a number 306042 and put the number in sequential order after the number 306042 like: 3060421
This comment was minimized by the moderator on the site
Hello, how i convert 13.09.2023 to 13.9 my numbers change into dates
This comment was minimized by the moderator on the site
I want to know how many times I went to check the inspection. which I recorded by days.
This comment was minimized by the moderator on the site
Hello. I would very much appreciate your help with this formula. I am wishing to enter the customer's date of birth in cell I3 (custom formatting mm/dd/yy) and would like that date of birth to automatically populate in A3 cell as mmddyy along with adding the First letter from First name cell F3 and First letter from Last name cell G3 in order to have it auto-populate an ID# for our customers in our database.

A3 F3 G3 I3
ID# First Name Last Name Date of Birth
030199AS Adam Smith 03/01/1999

Thank you so much for any help. You have no idea how much I appreciate it. I have been wracking my brain all night to no avail. :(
Shena Bruno
This comment was minimized by the moderator on the site
Hi, Shena, Bruno, in A3, please use the formula =TEXT(I3, mmddyyyy")&LEFT(F3,1)&LEFT(G3,1).
In the formula, it can be explained as two parts:
TEXT(I3, mmddyyyy") will change the value in I3 to this format mmddyyyy
LEFT(F3,1)&LEFT(G3,1) will extract the first character from left side of the strings in cell F3 and G3.
Hope it is helpful.
This comment was minimized by the moderator on the site
I want to convert the day (Tuesday) which is showing up as 1/3/1900 when you click on it because I added a formula on the original data to provide me with Day values. Now I want to use a pivot table to show me the actual days. How do I convert the 1/3/1900 which shows up at Tuesday to just Tuesday? 
This comment was minimized by the moderator on the site
Hi, Cathey, do you want to convert the formula to an actual value? If so, you can use the Paste as Value Only feature in Excel to copy and paste the formulas as values only, or you can use Kutools for Excel's To Actual feature to convert formulas to values.
This comment was minimized by the moderator on the site
Hi, I was trying to copy and past the value but its still showing the date as opposed to the day. 
This comment was minimized by the moderator on the site
Hi, Cathey, do you choose the Paste as Value option in right-click context menu? If you have choosed, try to press Ctrl +1 keys to enable format cells feature and change the cell format to mm/dd/yyyy in Date section.
This comment was minimized by the moderator on the site
Hello. I have the date 26.01.2021 in the cell A1 and in another cell I have the formula: =RIGHT(A1,4) to take the year from that date, and it returns “4526” instead of “2021”. Do you know how to solve it? Thank you very much!
This comment was minimized by the moderator on the site
try to change the format on cell A1 to general or number and then enter the values that you want again to see if it helps. I think your problem is the format issue.
This comment was minimized by the moderator on the site
Hi, I want to convert month to number. For exp: 8 months (date format) ---> "8" as a number or value. I've change its format but I can't count it.Because I want to put it to math format, for exp: 8-2 = 6 (8 from the months). But the 8 doesn't work.Hope you get it. Thank you for helping!
This comment was minimized by the moderator on the site
Hi i have a date in "13/02/2020 11:42:09" format and i want in MM/DD/YYYY format
This comment was minimized by the moderator on the site
Hi, just select the cell then apply the Text to Columns, in the Text to Columns, choose Delimiter > Space > Date(in the Date format, choose DMY) > Finish.
This comment was minimized by the moderator on the site
There is another way, it returns exactly the numeric format that represents the date abbreviated by (/), it would be: '=Substitute(A1;"/";"")
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations