如何让VBA的FileDateTime在Excel单元格输出英式日期而非美式日期

问题根源 #

你把日期对象转换成dd/mm/yyyy格式的字符串后再赋值给单元格，Excel会自动尝试解析这个字符串为日期值。当字符串中的日和月都小于等于12时（比如11/07/2023），Excel会按默认的日期解析规则（可能和系统区域设置存在歧义）将其识别为美式日期（月/日/年），导致日期颠倒。

解决方案 #

方法1：直接赋值日期对象，单独设置单元格格式（推荐） #

不要将日期转成字符串，直接把Date类型的变量赋值给单元格，然后设置单元格的数字格式为英式日期时间。这样单元格存储的是正确的日期数值，只是显示格式符合需求，完全避免解析错误。

修改后的代码：

Dim LastModified As Date
LastModified = FileDateTime("H:\Data\TestDb.accdb")
' 直接赋值日期对象
ActiveSheet.Cells(1, 2).Value = LastModified
' 设置单元格显示为英式日期时间格式
ActiveSheet.Cells(1, 2).NumberFormat = "dd/mm/yyyy hh:mm:ss"

方法2：强制Excel按指定格式解析字符串 #

如果必须使用字符串赋值，可以用CDate确保按系统区域规则解析，再搭配显示格式设置：

Dim LastModifiedString As String
Dim LastModified As Date
LastModified = FileDateTime("H:\Data\TestDb.accdb")
LastModifiedString = Format(LastModified, "dd/mm/yyyy hh:mm:ss")
' 按系统区域规则解析字符串为日期
ActiveSheet.Cells(1, 2).Value = CDate(LastModifiedString)
' 设置显示格式
ActiveSheet.Cells(1, 2).NumberFormat = "dd/mm/yyyy hh:mm:ss"

关键说明 #

Excel单元格中的日期本质是数值（从1900年1月1日开始的累计天数），显示格式和存储的数值是分离的。直接赋值日期对象能保证存储的数值正确，再通过设置NumberFormat控制显示样式，这是最稳妥的做法。

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