跳到主要內容

如何從Excel中的日期列表中提取或獲取年，月和日？

對於日期列表，您知道如何提取或獲取年，月和日的編號嗎？ 請參見下面的屏幕截圖。 在本文中，我們將向您展示從Excel中的日期列表中分別獲取年，月和日編號的公式。

從Excel中的日期列表中提取/獲取年，月和日

提取年號
提取月份號
提取天數

從Excel中的日期列表中提取/獲取年，月和日

以下面的日期列表為例，如果要從該列表中獲取年，月，日的數字，請按以下步驟進行。

提取年號

1.選擇一個用於查找年份的空白單元格，例如單元格B2。

2.複製並粘貼公式 =年（A2） 進入公式欄，然後按 Enter 鍵。 將“填充手柄”向下拖動到您需要從列表中獲取所有年份的範圍。

提取月份號

本節向您顯示從列表中獲取月份數的公式。

1.選擇一個空白單元格，複製並粘貼公式 = MONTH（A2） 進入公式欄，然後按 Enter 鍵。

2.將填充手柄向下拖動到所需的範圍。

然後，您將獲得日期列表的月份號。

提取天數

獲得日數的公式與上述公式一樣簡單。 請執行以下操作。

複製並粘貼公式 = DAY（A2） 進入空白單元格D2，然後按 Enter 鍵。 然後將“填充手柄”向下拖動到該範圍，以從引用的日期列表中提取所有日期。

現在，如上圖所示，從日期列表中提取了年，月和日的數字。

在Excel中輕鬆更改所選日期範圍內的日期格式

Excel的Kutools's 套用日期格式 實用程序可幫助您輕鬆地將所有日期格式更改為Excel中選定日期範圍內的指定日期格式。
立即下載Kutools for Excel的全功能30天免費試用版！

最佳辦公生產力工具

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

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

產品描述

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

  • 在Word，Excel，PowerPoint中啟用選項卡式編輯和閱讀，發布者，Access，Visio和Project。
  • 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
  • 將您的工作效率提高 50%，每天為您減少數百次鼠標點擊！
Comments (16)
No ratings yet. Be the first to rate!
CESAR22
CESAR22
This comment was minimized by the moderator on the site
EN MI CASO QUE TIENE 2022-07-01T00:00:01-06:00 PERO YO LO UNICO QUE NECESITO ES EL AÑO EL MES Y EL DIA

COMO PODRIA APLICAR A LA FORMULA
Reply
0
0
Danni
Danni     CESAR22
This comment was minimized by the moderator on the site
Hola Cesar22,

Lo que podes hacer es lo siguiente:
Si la informacion esta en A1
-=TEXT(LEFT(A1,10),"dd mm yyyy")

Esto saca la fecha de toda la otra informacion y te da el dia (dd), el mes (mm) y el año (yy).

Buena suerte
Reply
0
0
Ansonica Campher
Ansonica Campher
This comment was minimized by the moderator on the site
Hi, i have tried and these formulas do not seem to working for what i intend or understand. I need to extract the day and month from a cell.

Ex. 1955-03-08 (current data) to 8 Mar (required data)

Please could someone assist.
Reply
0
0
crystal
crystal     Ansonica Campher
This comment was minimized by the moderator on the site
Hi Ansonica Campher,
The following formula can do you a favor. Please give it a try.
=TEXT(A1,"dd mmm")
Reply
Report
0
0
Brittany
Brittany
This comment was minimized by the moderator on the site
HELP. I have a list of Payment dates, (8/1/16, 9/1/16, 10/1/16, + 30 years). How do I find the current month/year? Im trying to have my formula find the current month/year and grab the data from a different column from the row that the current month is in.
Reply
0
0
crystal
crystal     Brittany
This comment was minimized by the moderator on the site
Hi Brittany,
Supposing all dates are in column B and the current date is in F1. You need to create a helper column, enter the following formula into the first cell of that column (the cell should be in the same row as the first date in column B), press Enter to get the first result. Then drag its AutoFill Handle down to get the rest of the results.
If the result comes TRUE, it means that the date is the same month and year as the current date.
=MONTH(B1)&YEAR(B1)=MONTH($F$1)&YEAR($F$1)
Reply
Report
0
0
rheen
rheen
This comment was minimized by the moderator on the site
this website help me to find what ive been searching all this time.. many thanks..
Reply
0
0
frank
frank
This comment was minimized by the moderator on the site
This is stupid. Anyone who comes to this website wants the day of the month to populate into a cell! Anyone can plug in a random number into cell A2 and the click into cell B2 and insert the formula =A2. That's not what anyone is looking for that comes to this page. People want the cell to populate with the DAY OF THE MONTH!!!! This information and website is USELESS!!!
Reply
0
0
Charles
Charles     frank
This comment was minimized by the moderator on the site
You wanted the actual day of the week or month?? Day of the week would be...

=TEXT(B2,"dddd") for full day

=TEXT(B704,"ddd") for 3 letter day like Mon, Tue, Wed etc
Reply
0
0
Andrei
Andrei     Charles
This comment was minimized by the moderator on the site
That's amazing, thanks Charles, did not really seen this one coming.
Reply
0
0
Arun Kumar
Arun Kumar     Charles
This comment was minimized by the moderator on the site
i got what i need from this text function. Thank you
Reply
0
0
Khalysto
Khalysto     frank
This comment was minimized by the moderator on the site
Did you actually read the site? The formula =DAY(A2) would extract the day of the month from whatever date you had entered in cell A2. How is that not what you're asking for?
Reply
0
0
Andy
Andy     Khalysto
This comment was minimized by the moderator on the site
No, that would extract the day number, not the actual day. I believe frank means he wants to see MON, TUE, WED etc.
Reply
0
0
acat
acat
This comment was minimized by the moderator on the site
=MONTH(A2) drops the leading zero for single digit months. Having that cell formatted as Text does not solve. How to keep the leading zero?
Reply
0
0
Sonu Kumar Singh
Sonu Kumar Singh     acat
This comment was minimized by the moderator on the site
=text(A2, "mmm") for month name in abbreviations


=text(A2, "mmmm") for full month name
Reply
0
0
rheen
rheen     Sonu Kumar Singh
This comment was minimized by the moderator on the site
Thank u Sonu, u answered my question as well..
Reply
0
0
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations