跳到主要內容

如何在Excel中將各種非標準日期格式轉換為標準日期?

當您使用Excel工作簿時,您可能會遇到各種非標準日期格式。 Excel無法識別它們,您不能將它們用於任何計算目的,也不能根據需要更改它們的格式。 您如何將這些奇怪的日期格式更改為Excel標準日期?

使用公式將存儲為文本的日期轉換為正常的Excel日期
使用文本到列功能將數字轉換為正常的Excel日期
使用Kutools for Excel輕鬆將各種非標準日期轉換為正常Excel日期


使用公式將存儲為文本的日期轉換為正常的Excel日期

在這裡,我將討論使用DateValue函數將文本日期更改為正確的Excel日期。 輸入時 '2013 / 03 / 21 到單元格中,日期前的撇號用於向Excel表示該單元格中的值是文本值(請參見以下屏幕截圖)。 您可以按照以下步驟將此文本日期轉換為Excel日期:

doc-convert-normal-date1

1。 在相鄰的單元格B1中,輸入公式 =日期值(A1),請參見屏幕截圖:

doc-convert-normal-date2

2。 然後按 Enter 鍵,公式將返回數字41317。這是正確的答案,但是格式錯誤。 因此,您需要更改日期格式。

3。 選擇單元格B1並單擊鼠標右鍵,選擇 單元格格式 從上下文菜單中。 在裡面 單元格格式 對話框,單擊 聯繫電話 > 日期,然後根據需要選擇本地日期。 看截圖:

doc-convert-normal-date3

4。 然後點擊 OK,該數字已轉換為正常的Excel日期。 然後選擇單元格B1並將填充手柄拖到要包含此公式的範圍內。 看截圖:

doc-convert-normal-date4

尖端:也許您也有這種日期格式 2013年12月XNUMX日是, 日期值 函數也可以將其更改為Excel日期。 這樣做:

1。 輸入公式 = DATEVALUE(RIGHT(A1,2)&“-”&MID(A1,6,3)&“-”&LEFT(A1,4)) 進入空白單元格B1。 看截圖:

doc-convert-normal-date5

2。 然後點擊 Enter 鍵,選擇單元格B1,然後將填充手柄拖到所需的範圍。 看截圖:

doc-convert-normal-date6

這只是兩個簡單的示例,如果您有任何其他文本日期格式,則需要其他公式來處理。

一鍵式將各種非標準日期轉換為Excel中的正常日期:

轉換為日期 的效用 Excel的Kutools 可以幫助您在Excel中一鍵快速將各種非標準日期同時轉換為正常日期。 立即下載 Kutools for Excel! (30 天免費試用)


使用文本到列功能將數字轉換為正常的Excel日期

有時您可能會有一些數字,例如 20130321 在單元格中,但現在您要將其轉換為正常的Excel日期 3/21/2013是, 文本到列 工具可以將數字轉換為公認的Excel日期。

1。 選擇要更改的單元格。

2。 點擊 數據 > 文本到列將文本轉換為列嚮導,檢查 分隔,然後點擊 下一頁.

doc-convert-normal-date7

3。 在步驟2中,直接單擊 下一頁 以繼續。

doc-convert-normal-date8

4。 在第3步中,選擇 日期 來自 列數據格式,並選擇 年月日 來自 日期 下拉列表。 看截圖:

doc-convert-normal-date9

5。 然後點擊,並且所選單元格中的數字已更改為正常的Excel日期。 查看屏幕截圖:

doc-convert-normal-date10 -2 doc-convert-normal-date11

使用Kutools for Excel將各種非標準日期轉換為正常Excel日期

不同的非標準日期需要不同的解決方案,因此我們需要一種方便且多功能的方法,該方法可以幫助我們將各種奇怪的日期同時更改為正常的Excel日期。 隨著 轉換為日期 of Excel的Kutools,您可以快速轉換某些非標準日期格式,該格式無法通過Excel識別為正常日期格式。

申請前 Excel的Kutools首先下載並安裝.

1。 選擇要轉換的非標準日期範圍,然後單擊 庫工具 > 內容 > 轉換為日期,請參見屏幕截圖:

2.然後 轉換為日期 彈出對話框,您可以看到所選範圍內的所有非標準日期都同時轉換為標準日期格式。 看截圖:

  如果您想免費試用(30天)此實用程序, 請點擊下載,然後按照上述步驟進行操作。


演示:將各種非標準日期格式轉換為標準日期

最佳辦公生產力工具

熱門特色: 尋找、突出顯示或識別重複項   |  刪除空白行   |  合併列或儲存格而不遺失數據   |   沒有公式的回合 ...
超級查詢: 多條件VLookup    多值VLookup  |   跨多個工作表的 VLookup   |   模糊查詢 ....
高級下拉列表: 快速建立下拉列表   |  依賴下拉列表   |  多選下拉列表 ....
欄目經理: 新增特定數量的列  |  移動列  |  切換隱藏列的可見性狀態  |  比較範圍和列 ...
特色功能: 網格焦點   |  設計圖   |   大方程式酒吧    工作簿和工作表管理器   |  資源庫 (自動文字)   |  日期選擇器   |  合併工作表   |  加密/解密單元格    按清單發送電子郵件   |  超級濾鏡   |   特殊過濾器 (過濾粗體/斜體/刪除線...)...
前 15 個工具集12 文本 工具 (添加文本, 刪除字符,...)   |   50+ 圖表 類型 (甘特圖,...)   |   40+ 實用 公式 (根據生日計算年齡,...)   |   19 插入 工具 (插入二維碼, 從路徑插入圖片,...)   |   12 轉化 工具 (數字到單詞, 貨幣兌換,...)   |   7 合併與拆分 工具 (高級合併行, 分裂細胞,...)   |   ... 和更多

使用 Kutools for Excel 增強您的 Excel 技能,體驗前所未有的效率。 Kutools for Excel 提供了 300 多種進階功能來提高生產力並節省時間。  點擊此處獲取您最需要的功能...

kte選項卡201905


Office選項卡為Office帶來了選項卡式界面,使您的工作更加輕鬆

  • 在Word,Excel,PowerPoint中啟用選項卡式編輯和閱讀,發布者,Access,Visio和Project。
  • 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
  • 將您的工作效率提高 50%,每天為您減少數百次鼠標點擊!
Comments (36)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
How to convert dd.mm.yyyy to excel date format
This comment was minimized by the moderator on the site
Hello Soon,

Glad to help. I have solution for this. Just use the second method in the article: Convert Numbers To Normal Excel Date With Text To Columns Function. Please follow the instructions in the method, and just make sure you choose DMY from the Date drop down list in step 4. See screenshot. Then click Finish to get the correct result, which is 6/21/2022.

Please have a try.

Sincerely,
Mandy
This comment was minimized by the moderator on the site
Taking A1 as the cell which has the date formatted as dd.mm.yyyy, ~

Formula----> =TEXT(DATEVALUE(SUBSTITUTE(A1,".","-")),"mm-dd-yyyy")
This comment was minimized by the moderator on the site
Hello Siddhartha,

Thank you for your reply. But the formula doesn't work in my case. For example, I have date 21.06.2022 in cell A1. Use your formula and it returns the #VALUE! error value.

I have solution for this. Just use the second method in the article: Convert Numbers To Normal Excel Date With Text To Columns Function. Please follow the instructions in the method, and just make sure you choose DMY from the Date drop down list in step 4. See screenshot. Then click finish to get the correct result, which is 6/21/2022.

Please have a try.

Sincerely,
Mandy
This comment was minimized by the moderator on the site
How do I remove the time form the date in excel?
This comment was minimized by the moderator on the site
Hello Harish,

Glad to help. Please see our article: https://www.extendoffice.com/documents/excel/875-excel-remove-time-from-date.html. In this article lists four methods to remove time from date. Please have a try. Have a nice day.

Sincerely,
Mandy
This comment was minimized by the moderator on the site
Select the column where that dates are > Ctrl+H > Find value as " *"; Replace value as blank > Replace all.
The date format would look something like this - dd-mm-yyyy 00:00:00.
Then change the date format to "Short Date".

Job done!
This comment was minimized by the moderator on the site
Hello Siddhartha,

Thank you again for your help. Have a nice day.

Sincerely,
Mandy
This comment was minimized by the moderator on the site
how do i convert 2109 (YYMM) into 09/2021
This comment was minimized by the moderator on the site
=TEXT(DATE(YEAR(LEFT(F10,2))+(YEAR(TODAY())-YEAR(LEFT(F10,2)))-(TEXT(TODAY(),"yy")-LEFT(F10,2)),RIGHT(F10,2),1),"mm/yyyy")
This comment was minimized by the moderator on the site
Hello Siddhartha,

Thank you for your help. Your formula works perfectly. Have a nice day.

Sincerely,
Mandy
This comment was minimized by the moderator on the site
I wanted to convert 1529557740 to this format Fri 22 Jun 2018 00:09:00. It didn't work. Can someone suggest
This comment was minimized by the moderator on the site
Found the answer: A2 / 86400 + 25569 - 5 / 24 (where A2 is column name and -5 UTC time offset for CST)
This comment was minimized by the moderator on the site
how do o convert from yyyyy-mm-ddd hh mm ss to yyyy-mm-dd?
This comment was minimized by the moderator on the site
how convert 2/1/2018 5:54:58 PM to Feb 01, 2018 3:08 PM
This comment was minimized by the moderator on the site
how to convert 12/14 to December 2014
This comment was minimized by the moderator on the site
Hi Team, how to convert date format from dd/mm/yyyy hh:mm:ss to dd-mm-yyyy hh:mm:ss by using any formula or simple step also. when am trying to do the format cells to change the date format, cells are remain same.pls do the needful.
This comment was minimized by the moderator on the site
Check out the example and formula of how to convert the exact data format which you want in the attached screen shot.
This comment was minimized by the moderator on the site
How to convert date format, '20.06.2016' to '20-06-2016' for applying the text formula?
This comment was minimized by the moderator on the site
Assuming the date is in cell A2, enter the following into cell B2 =SUBSTITUTE(A2,".","/")
This comment was minimized by the moderator on the site
=Text(dd-mm-yyyy)
This comment was minimized by the moderator on the site
I have an excel file that has a countdown function between time NOW and a future date/time. Works fine as long as I open the file every time on a laptop but when opened with a cell phone (IOS), the countdown calculates always to today's date as opposed to an assigned future date. Any thoughts?
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