如何在R中正确转换Excel导入的Windows日期格式

问题背景 #

从Excel导入的数据中，日期列被识别为字符型的数字序列号（如"29221"对应Excel中的1980-01-01），直接使用as.Date()转换会得到错误的未来日期——这是因为R默认的日期起始点（1970-01-01，Unix纪元）与Excel的日期起始规则不同。

测试用的日期向量：

dates <- c("29221", "38718", "44986", "40360", "38808", "41487", "40544", "30682", "NA", "NA", "37987", "36526", "41640", "40909", "44896", "38718", "37622", "35796", "41275", "43466", "43101", "40909", "37257", "39814", "44562", "37987", "37987", "40179", "35431", "39448", "40909", "43101", "40915", "38718", "35065", "41640", "40909", "36161", "42491", "38718", "39825", "40909")

解决方案 #

方法1：基础R实现 #

Excel的日期序列号以1900-01-01为起始，但由于Excel存在1900年闰年的历史bug，实际需要将起始点设为1899-12-30才能正确转换。步骤如下：

1. 将字符型的序列号转换为数值型，保留NA值；
2. 使用as.Date()并指定origin参数为"1899-12-30"。

代码示例：

# 转换字符向量为数值型
dates_num <- as.numeric(dates)
# 转换为YYYY-MM-DD格式的日期
correct_dates <- as.Date(dates_num, origin = "1899-12-30")

# 查看前6个转换结果，与Excel原始日期匹配
head(correct_dates)
# [1] "1980-01-01" "2006-01-01" "2023-03-01" "2010-07-01" "2006-04-01" "2013-08-01"

方法2：使用readxl包（更简便） #

如果数据是通过readxl包导入的，推荐使用专门处理Excel日期的函数，无需手动处理起始点：

library(readxl)

# 直接转换已导入的序列号
correct_dates <- excel_numeric_to_date(as.numeric(dates))

# 若还未导入数据，可在导入时直接指定日期列类型
# df <- read_excel("your_file.xlsx", col_types = c("date", "numeric", ...))

excel_numeric_to_date()会自动处理Excel的1900闰年bug，转换结果更可靠。

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