如何在Excel中为日期动态添加序数后缀（1st、2nd等）？

针对将6/1/2024这类日期转换为01st Jun 2024格式的需求，除了辅助列+VLOOKUP，还有以下几种更高效的替代方案：

1. 单公式直接生成（所有Excel版本通用） #

无需辅助列，直接在目标单元格（如B1）输入嵌套逻辑的TEXT函数组合，自动匹配序数词后缀：

=TEXT(A1,"dd")&IF(AND(DAY(A1)>10,DAY(A1)<14),"th",CHOOSE(MOD(DAY(A1),10)+1,"th","st","nd","rd","th","th","th","th","th","th"))&" "&TEXT(A1,"mmm yyyy")

公式逻辑说明： #

• TEXT(A1,"dd")：提取两位数字格式的日期（如01）
• 核心判断：先排除11-13号（这类日期不管个位数字，统一用th），再根据日期个位数字匹配后缀：1→st、2→nd、3→rd，其余→th
• TEXT(A1,"mmm yyyy")：提取月份缩写+年份（如Jun 2024）

2. LET函数简化公式（Excel 365/2021及以上版本） #

利用LET函数将重复计算的变量存储，让公式更易读和维护：

=LET(
  dayNum, DAY(A1),
  dayText, TEXT(A1,"dd"),
  suffix, IF(AND(dayNum>10,dayNum<14),"th",CHOOSE(MOD(dayNum,10)+1,"th","st","nd","rd","th","th","th","th","th","th")),
  monthYear, TEXT(A1,"mmm yyyy"),
  dayText&suffix&" "&monthYear
)

3. Power Query批量转换（适合大量数据） #

如果需要处理成百上千条日期，用Power Query可以批量完成，无需下拉公式：

1. 选中A列日期数据→点击「数据」选项卡→「从表格/区域」（勾选「我的表格有标题」）
2. 在Power Query编辑器中，选中日期列→「转换」选项卡→「格式」→「自定义格式」，输入dd mmm yyyy，生成基础格式的日期
3. 添加自定义列，输入以下M语言公式：

let
    dayPart = Date.Day([日期]),
    suffix = if dayPart >=11 and dayPart <=13 then "th" 
             else switch(Number.Mod(dayPart,10),
                 1, "st",
                 2, "nd",
                 3, "rd",
                 "th")
in
    Text.Start([自定义格式],2) & suffix & Text.Range([自定义格式],2)

4. 点击「关闭并上载」，转换后的结果会自动导入Excel表格

4. VBA宏一键批量转换（适合重复操作场景） #

如果需要频繁做这类转换，可以写个宏一键完成：

1. 按Alt+F11打开VBA编辑器→插入「模块」
2. 粘贴以下代码：

Sub ConvertDateWithOrdinal()
    Dim rng As Range
    Dim cell As Range
    Dim dayNum As Integer
    Dim suffix As String
    
    Set rng = Application.Selection '选中需要转换的日期单元格
    
    For Each cell In rng
        If IsDate(cell.Value) Then
            dayNum = Day(cell.Value)
            '判断序数词后缀
            Select Case dayNum
                Case 11 To 13
                    suffix = "th"
                Case Else
                    Select Case dayNum Mod 10
                        Case 1: suffix = "st"
                        Case 2: suffix = "nd"
                        Case 3: suffix = "rd"
                        Case Else: suffix = "th"
                    End Select
            End Select
            '输出到右侧单元格
            cell.Offset(0, 1).Value = Format(cell.Value, "dd") & suffix & " " & Format(cell.Value, "mmm yyyy")
        End If
    Next cell
End Sub

3. 使用时：选中A列日期→按Alt+F8选择ConvertDateWithOrdinal→点击「运行」，结果自动写入B列

内容的提问来源于stack exchange，提问作者Manoj