英文Excel中转换荷兰语日期字符串的解决方案求助

我之前也碰到过一模一样的问题——英文Excel死活认不出荷兰语的日期字符串，还不能改系统语言，折腾了好一阵才找到几个靠谱的解决方案，都能适配多系统，给你唠唠：

方法一：纯公式拆分转换（无代码） #

如果只是处理少量数据，用Excel自带公式就能搞定，不需要宏或者插件：

• 先提取日期中的日：=LEFT(A1,FIND(" ",A1)-1)
• 然后处理月份：荷兰语和英语月份的对应关系是固定的，用SWITCH函数（Excel 2019及以后版本支持）直接映射：

  =SWITCH(MID(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1)+1)-FIND(" ",A1)-1),
          "juli","July","januari","January","februari","February",
          "maart","March","april","April","mei","May","juni","June",
          "augustus","August","september","September","oktober","October",
          "november","November","december","December")
  

• 再提取年份：=RIGHT(A1,4)
• 最后把三部分拼接成英文日期字符串，转成标准日期格式，整合后的完整公式：

  =DATEVALUE(LEFT(A1,FIND(" ",A1)-1)&" "&
             SWITCH(MID(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1)+1)-FIND(" ",A1)-1),
                    "juli","July","januari","January","februari","February",
                    "maart","March","april","April","mei","May","juni","June",
                    "augustus","August","september","September","oktober","October",
                    "november","November","december","December")&" "&
             RIGHT(A1,4))
  

  要是你用的是旧版Excel（不支持SWITCH），可以做个隐藏的月份映射表（比如Sheet2的A列放荷兰语月份，B列放英语月份），用VLOOKUP替代：

  =DATEVALUE(LEFT(A1,FIND(" ",A1)-1)&" "&
             VLOOKUP(MID(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1)+1)-FIND(" ",A1)-1),Sheet2!A:B,2,FALSE)&" "&
             RIGHT(A1,4))
  

方法二：VBA宏批量处理（高效） #

如果要处理大量数据，写个VBA自定义函数会方便很多，而且跨系统通用：

1. 按Alt+F11打开VBA编辑器，插入一个新模块
2. 粘贴以下代码：

   Function DutchToDate(dutchDate As String) As Date
       Dim monthMap As Object
       Dim parts() As String
       Dim englishMonth As String
       
       ' 创建荷兰语→英语月份的映射字典
       Set monthMap = CreateObject("Scripting.Dictionary")
       With monthMap
           .Add "januari", "January"
           .Add "februari", "February"
           .Add "maart", "March"
           .Add "april", "April"
           .Add "mei", "May"
           .Add "juni", "June"
           .Add "juli", "July"
           .Add "augustus", "August"
           .Add "september", "September"
           .Add "oktober", "October"
           .Add "november", "November"
           .Add "december", "December"
       End With
       
       ' 拆分日期字符串为日、月、年三部分
       parts = Split(dutchDate, " ")
       If UBound(parts) <> 2 Then
           DutchToDate = CVErr(xlErrValue) ' 格式错误返回值错误
           Exit Function
       End If
       
       ' 匹配英语月份
       If monthMap.Exists(LCase(parts(1))) Then
           englishMonth = monthMap(LCase(parts(1)))
       Else
           DutchToDate = CVErr(xlErrValue) ' 未知月份返回错误
           Exit Function
       End If
       
       ' 转换为标准日期
       DutchToDate = DateValue(parts(0) & " " & englishMonth & " " & parts(2))
   End Function
   

3. 回到Excel，在任意单元格输入=DutchToDate(A1)，就能直接把A1单元格的荷兰语日期转成标准日期格式，还能自动处理错误输入。

方法三：Power Query可视化处理（适合大数据） #

Power Query（Excel 2016及以后内置，旧版可装插件）是处理批量数据的神器，操作可视化，不用写复杂公式：

1. 选中你的日期数据列，点击数据选项卡 → 从表格/范围（旧版是获取外部数据→自表格/范围）
2. 在Power Query编辑器中，选中日期列，点击转换→拆分列→按分隔符，选空格作为分隔符，拆分成3列（日、荷兰语月份、年）
3. 选中荷兰语月份列，点击添加列→自定义列，输入月份映射公式：

   = switch([荷兰语月份],
       "januari", "January",
       "februari", "February",
       "maart", "March",
       "april", "April",
       "mei", "May",
       "juni", "June",
       "juli", "July",
       "augustus", "August",
       "september", "September",
       "oktober", "October",
       "november", "November",
       "december", "December")
   

4. 新增一个自定义列，把日、转换后的英语月份、年拼接成字符串：= [日] & " " & [自定义列] & " " & [年]
5. 选中拼接后的列，点击转换→数据类型→日期，Power Query会自动把字符串转成标准日期
6. 最后点击关闭并上载，把转换好的数据导入Excel即可

这三种方法都不需要修改系统语言，完全适配多系统环境，你可以根据数据量和自己的操作习惯选最合适的~

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