跳到主要内容

如何从Excel中的日期列表中提取或获取年,月和日?

对于日期列表,您知道如何提取或获取年,月和日的编号吗? 请参见下面的屏幕截图。 在本文中,我们将向您展示从Excel中的日期列表中分别获取年,月和日编号的公式。


从Excel中的日期列表中提取/获取年,月和日

以下面的日期列表为例,如果要从该列表中获取年,月,日的数字,请按以下步骤进行。

提取年号

1.选择一个用于查找年份的空白单元格,例如单元格B2。

2.复制并粘贴公式 =年(A2) 进入公式栏,然后按 输入 键。 将“填充手柄”向下拖动到您需要从列表中获取所有年份的范围。

提取月份号

本节向您显示从列表中获取月份数的公式。

1.选择一个空白单元格,复制并粘贴公式 = MONTH(A2) 进入公式栏,然后按 输入 键。

2.将填充手柄向下拖动到所需的范围。

然后,您将获得日期列表的月份号。

提取天数

获得日数的公式与上述公式一样简单。 请执行以下操作。

复制并粘贴公式 = DAY(A2) 进入空白单元格D2,然后按 输入 键。 然后将“填充手柄”向下拖动到该范围,以从引用的日期列表中提取所有日期。

现在,如上图所示,从日期列表中提取了年,月和日的数字。


在Excel中轻松更改所选日期范围内的日期格式

Kutools for Excel'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 Tab 为 Office 带来选项卡式界面,让您的工作更加轻松

  • 在Word,Excel,PowerPoint中启用选项卡式编辑和阅读,发布者,Access,Visio和Project。
  • 在同一窗口的新选项卡中而不是在新窗口中打开并创建多个文档。
  • 每天将您的工作效率提高50%,并减少数百次鼠标单击!
Comments (16)
No ratings yet. Be the first to rate!
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
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
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.
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")
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.
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)
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..
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!!!
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
This comment was minimized by the moderator on the site
That's amazing, thanks Charles, did not really seen this one coming.
This comment was minimized by the moderator on the site
i got what i need from this text function. Thank you
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?
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.
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?
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
This comment was minimized by the moderator on the site
Thank u Sonu, u answered my question as well..
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations