跳到主要内容

在Excel中输入或更改日期后,如何自动对日期进行排序? 

在Excel中, 排序 函数可以帮助您根据需要以升序或降序对日期进行排序。 但这不是动态的,如果您对日期进行了排序,然后向其添加了新日期,则需要再次对其进行排序。 在工作表中每次输入新日期时,是否有任何好的快速方法可以自动对日期进行排序?

使用公式输入或更改日期时自动排序日期

使用VBA代码输入或更改日期时自动排序日期


箭头蓝色右气泡 使用公式输入或更改日期时自动排序日期

例如,A列中的原始日期,以下公式可帮助您根据要排序的列自动对新助手列中的日期或任何其他文本字符串进行排序,请执行以下操作:

1。 输入以下公式:

=INDEX($A$2:$A$15,MATCH(ROWS($A$2:A2),COUNTIF($A$2:$A$15,"<="&$A$2:$A$15),0)) 放入日期列旁边的空白单元格中, C2,例如,然后按 Ctrl + Shift + Enter 键,您将获得一个数字序列,然后将填充手柄向下拖动到要使用的单元格,请参见屏幕截图:

备注:在上式中: A2:A15 是您要自动排序的原始日期范围。

doc按日期自动排序1

2. 然后通过单击将数字格式化为日期格式 短期约会 来自 总类 下的下拉列表 首页 标签,请参见屏幕截图:

doc按日期自动排序2

3。 然后,序列号已转换为日期格式,并且原始日期也已排序,请参见屏幕截图:

doc按日期自动排序3

4。 从现在开始,当您输入新日期或更改A列中的日期时,C列中的日期将自动按升序排序,请参见屏幕截图:

doc按日期自动排序4


箭头蓝色右气泡 使用VBA代码输入或更改日期时自动排序日期

当您输入新日期或根据需要更改日期时,以下VBA代码可以帮助您自动对原始列中的日期进行排序。

1。 输入或更改日期时,转到要自动对日期进行排序的工作表。

2。 右键单击工作表标签,然后选择 查看代码 从上下文菜单中,弹出 Microsoft Visual Basic应用程序 窗口,请复制以下代码并将其粘贴到空白处 模块 窗口,请参见屏幕截图:

VBA代码:输入或更改日期时自动排序:

Private Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice
    On Error Resume Next
    If Application.Intersect(Target, Application.Columns(1)) Is Nothing Then Exit Sub
    If Target.Count > 1 Then Exit Sub
    Range("A1").Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes, _
                                        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub

doc按日期自动排序6

备注:在上面的代码中,输入的日期将在A列中自动排序,您可以更改 A1A2 放到自己需要的单元格中。

3。 从现在开始,当您在A列中输入日期时,该日期将自动升序排序。

最佳办公生产力工具

热门特色: 查找、突出显示或识别重复项   |  删除空白行   |  合并列或单元格而不丢失数据   |   不使用公式进行四舍五入 ...
超级查询: 多条件VLookup    多值VLookup  |   跨多个工作表的 VLookup   |   模糊查询 ....
高级下拉列表: 快速创建下拉列表   |  依赖下拉列表   |  多选下拉列表 ....
列管理器: 添加特定数量的列  |  移动列  |  切换隐藏列的可见性状态  |  比较范围和列 ...
特色功能: 网格焦点   |  设计图   |   大方程式酒吧    工作簿和工作表管理器   |  资源库 (自动文本)   |  日期选择器   |  合并工作表   |  加密/解密单元格    按列表发送电子邮件   |  超级筛选   |   特殊过滤器 (过滤粗体/斜体/删除线...)...
前 15 个工具集12 文本 工具 (添加文本, 删除字符,...)   |   50+ 图表 类型 (甘特图,...)   |   40+ 实用 公式 (根据生日计算年龄,...)   |   19 插入 工具 (插入二维码, 从路径插入图片,...)   |   12 转化 工具 (小写金额转大写, 货币兑换,...)   |   7 合并与拆分 工具 (高级组合行, 分裂细胞,...)   |   ... 和更多

使用 Kutools for Excel 增强您的 Excel 技能,体验前所未有的效率。 Kutools for Excel 提供了 300 多种高级功能来提高生产力并节省时间。  单击此处获取您最需要的功能...

kte选项卡201905


Office Tab 为 Office 带来选项卡式界面,让您的工作更加轻松

  • 在Word,Excel,PowerPoint中启用选项卡式编辑和阅读,发布者,Access,Visio和Project。
  • 在同一窗口的新选项卡中而不是在新窗口中打开并创建多个文档。
  • 每天将您的工作效率提高50%,并减少数百次鼠标单击!
Comments (15)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Hello,This is a great tool. thank you. How can i apply this to multiple columns in the same tab? Could i apply it to restart sorting by date in a new cell of the same column? Would i just repaste the VBA code into the same window?
Thank you.
This comment was minimized by the moderator on the site
Hello Noname9,How are you? To achieve your goal by using VBA code is beyond my reach. But I do know how to use formulas to do the trick.Suppose we have two columns of dates, say A2:B7. How to sort these dates into a new column? Please do as follows.
First, we need to combine the two columns of dates into one column. Copy and paste the formula =INDEX($A$2:$B$7,INT((ROWS(D$2:D2)-1)/2)+1,MOD(ROWS(D$2:D2)-1,2)+1) into cell D2. And drag the fill handle down to combine all dates. Please see screenshot 1.
Then, we will sort the combined dates. Copy and paste the formula =INDEX($D$2:$D$13,MATCH(ROWS($D$2:D2),COUNTIF($D$2:$D$13,"<="&$D$2:$D$13),0)) into F2. And drag the fill handle down to sort all dates. Please see screenshot 2.
Hope it will help. Have a nice day.Sincerely,Mandy
This comment was minimized by the moderator on the site
Hello,What if i want to do this to multiple columns or even have anew start point in the same column? Do i just do a break and recopy the VBA code in that same window?
Thank you.
This comment was minimized by the moderator on the site
That VBA code is solid gold! Thank you! :-)
This comment was minimized by the moderator on the site
With the VBA code, I have copy and pasted the above but wish for the dates in column F to be the values by which the data is sorted. I've changed the range values to F2 and F3500 (the size of the spreadsheet where row 1 is titles), but it still sorts by the dates in column A. Can somebody help me please?
This comment was minimized by the moderator on the site
Hello, Ross,
When applying the code to column F, you should change some references to your need as below code:
Private Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice 20160606
On Error Resume Next
If Application.Intersect(Target, Application.Columns(6)) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
Range("F1").Sort Key1:=Range("F2"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub

Please try, hope it can help you!
This comment was minimized by the moderator on the site
Is there a particular formula to keep the cells following the sorted date? It would be nice to organize by date but keep the entire row of information. Any help would be much appreciated.
This comment was minimized by the moderator on the site
I mad a checkbook register and it works but I want to figure out how to make my entry’s to go into date order. Any help would be appreciated. I’m still learning excel.
This comment was minimized by the moderator on the site
In addition to the duplicate dates, is there also a way to include multiple columns of data when it sorts? I need it to include multiple columns and sort them all together with the expiration dates.
This comment was minimized by the moderator on the site
how can I do this same sorting calculation but from newest date to oldest? Currently it is Oldest to Newest. Flipping the < sign isn't enough and beyond that I don't have a strong enough understanding of what it is doing. Also I think what may be happening is excel automatically works top to bottom causing difficulties.
This comment was minimized by the moderator on the site
Hello, Bo,

To auto sort the date from newest to oldest, you just need to change the <= to >= in the above formula as follows:
=INDEX($A$2:$A$15,MATCH(ROWS($A$2:A2),COUNTIF($A$2:$A$15,">="&$A$2:$A$15),0))
After inserting this formula, please remember to press Ctrl + Shift + Enter keys together to get the correct result.
Please try it.
This comment was minimized by the moderator on the site
What if there is a duplicate date in the list? And I want both numbers to show up.
This comment was minimized by the moderator on the site
Hello, Ryan,

To sort the date with duplicate ones, you should apply the following formula:

=IFERROR(INDEX($A$2:$A$11,MATCH(ROWS($A$2:A2),COUNTIF($A$2:$A$11,"<="&$A$2:$A$11),0)),IF(ROWS($A$2:A2)<ROWS($A$2:$A$11),B3,""))

Please remember to press Shift + Ctrl + Enter keys together.

Hope it can help you, thank you!
This comment was minimized by the moderator on the site
Awesome :) Working fine
This comment was minimized by the moderator on the site
U forgot to mention the formula is array and you need to ctrl+Shift+ enter. Luckily you had a screenshot or your page would be a waste of cyberspace
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations