☰

Excel 中单列中有多种日期格式

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2016 date
Excel 中单列中有多种日期格式

我有一个 csv 文件，其中有一个日期列，其中包含各种形式的日期，如下所示：

dd-mm-yyyy

yyyy-mm-dd

yyyy-mm

yyyy

这些不规则数据位于单列中。

如何将它们全部转换为dd-mm-yyyy并使用 Excel 公式从中提取年份？所需的输出如下：

Input       Output
25-03-1954  25-03-1954
22-09-1987  22-09-1987
1990-01-25  25-01-1990
1968-11-15  15-11-1968
1919-01     01-01-1919
1873-02     01-02-1873
1945        01-01-1945
1933        01-01-1933

答案1

如果您真正需要的是年份，那么请使用@robinCTS 公式。

如果您希望结果是“真实的 Excel 日期”，并且

  • 如果你的数据是文本而不是真实日期，
  • 如果你的区域日期设置DMY是
  • 并且该列的格式为dd-mm-yyyy

那么你可以使用这个公式：

=IFERROR(IFERROR(IFERROR(DATEVALUE(A2),DATEVALUE(A2&"-01")),DATEVALUE(A2&"-01-01")),"illegal date format")

请注意，1900 年之前的日期在 Excel 中不能是真实日期，但可以表示为文本字符串。我将把调整公式的任务留给您。

如果您的输出需要国际化，以适应具有其他日期格式的国家/地区，那么 VBA 解决方案可能是最佳选择。如果无法实现，可以使用辅助列来拆分数据，然后适当地重新组合，或者使用非常复杂的公式来执行相同操作。

请注意，此 UDF 将输出实际日期或仅输出年份，具体取决于第二个可选参数。

Option Explicit
Function ConvertToDate(S As String, Optional Yr As Boolean = False) As Variant
    Dim V
    Dim dtTemp As Date

V = Split(S, "-")
Select Case UBound(V)
    Case 0
        dtTemp = DateSerial(V(0), 1, 1)
    Case 1
        dtTemp = DateSerial(V(0), V(1), 1)
    Case 2
        If Len(V(0)) = 4 Then
            dtTemp = CDate(S)
        Else
            dtTemp = DateSerial(V(2), V(1), V(0))
        End If
End Select

If Yr = True Then
    ConvertToDate = Year(dtTemp)
Else
    If Year(dtTemp) < 1900 Then
        ConvertToDate = Format(dtTemp, "dd-mm-yyyy")
    Else
        ConvertToDate = dtTemp
    End If
End If

End Function

两种方法都查看第一个段，并假设它是年份LEN > 4，然后查看有多少个段来决定是否附加必要-01的

VBA 解决方案将把 1900 年之前的日期输出为文本字符串。

答案2

由于输入数据包含 1900 年之前的日期，因此事情会变得有些棘手。处理此问题的最简单方法是专门处理日期的文本表示。

如果您实际上不需要完整日期，而只需要年份，请在行中的任意单元格中输入此基本公式2:2：

=IFERROR(VALUE(LEFT(A2,4)),VALUE(RIGHT(A2,4)))

对于完整日期，公式稍微复杂一些：

=IF(MID(A2,3,1)="-",A2,CHOOSE((LEN(A2)-4)/3+1,"01-01","01"&MID(A2,5,3),RIGHT(A2,2)&MID(A2,5,3))&"-"&LEFT(A2,4))

解释：

完整日期公式的美化版本如下：

=
IF(
  MID(A2,3,1)="-",
  A2,
  CHOOSE(
    (LEN(A2)-4)/3+1,
    "01-01",
    "01"&MID(A2,5,3),
    RIGHT(A2,2)&MID(A2,5,3)
  )
  &"-"&LEFT(A2,4)
)

这很简单。唯一有点棘手的是CHOOSE()函数的第一个参数(LEN(A2)-4)/3+1。它将输入日期字符串的长度映射到基于 1 的索引，即→ → 。[yyyy, yyyy-mm, yyyy-mm-dd, dd-mm-yyyy][4, 7, 10, 10][1, 2, 3, 3]

笔记：

  • 美化后的公式确实可以起作用，只要输入即可。

注意事项：

  • 这些公式假设所有输入日期都以文本形式存储。（这些公式可以轻松修改，以适应以序列号形式存储的日期。）
  • 如果输入的日期包含前导/尾随空格，上述公式可能无法正常工作。它们肯定不会在日期字符串的其他地方使用空格（或其他字符）。将公式A2中的所有 s 替换为TRIM(A2)，或者最好替换为SUBSTITUTE(A2," ","")，将解决此问题。

相关内容