使用R的read_excel读取Excel时如何处理异常日期格式？

问题根源 #

• Excel的日期本质是浮点数存储：整数部分代表1899-12-30（1900日期系统，Windows默认）或1904-01-01（1904日期系统，旧版Mac默认）起的天数，小数部分是一天内的时间比例。
• 你的Excel日期列存在混合格式：部分单元格是标准日期格式（read_excel自动识别为Date类型），部分是数值/文本格式（被读取为原始数字或字符串），导致导入后格式混乱。
• 可能存在双日期系统混用：如果文件里部分数据来自使用1904日期系统的Excel，用1899-12-30作为origin转换会得到错误日期，出现无法识别的数字。

解决方法 #

第一步：先排查Excel源文件 #

• 选中目标日期列，统一设置为「日期」格式（选择dd/mm/yyyy样式），保存后重新导入，很多时候能直接解决自动识别问题。
• 检查Excel的日期系统：打开文件→文件→选项→高级→「计算此工作簿时」，查看是否勾选了「使用1904日期系统」，记下来后续转换备用。

第二步：R中批量处理混合格式列 #

方法1：强制读取为文本后统一解析

先把目标列按文本读取，避免read_excel自动识别出错，再用lubridate工具处理：

library(readxl)
library(tidyverse)
library(lubridate)

# 读取时指定目标列为文本类型，其他列按需设置（比如"numeric"）
df <- read_excel("your_file.xlsx", col_types = c("text", ...))

# 自动识别文本日期+Excel数值日期两种格式
df$date_col <- parse_date_time(df$date_col, orders = c("dmy", "Excel"))

# 如果只需要纯日期部分，转成Date类型
df$date_col <- as.Date(df$date_col)

方法2：分情况手动处理（更可控）

如果自动识别不准确，可手动判断数值/文本类型分别转换：

df <- df %>%
  mutate(
    date_col = case_when(
      # 处理1900系统的数值日期
      str_detect(date_col, "^\\d+$") & as.integer(date_col) >= 1 ~ 
        as.Date(as.integer(date_col), origin = "1899-12-30"),
      # 处理1904系统的数值日期（数值通常小于10000）
      str_detect(date_col, "^\\d+$") & as.integer(date_col) < 10000 ~ 
        as.Date(as.integer(date_col), origin = "1904-01-01"),
      # 处理dd/mm/yyyy格式的文本日期
      TRUE ~ dmy(date_col)
    )
  )

方法3：处理带时间的日期值

如果数字是带小数的浮点数（比如45678.25代表日期+6小时），需要转成datetime再提取日期：

# 转成带时间的datetime格式
df$date_time_col <- as.POSIXct(as.numeric(df$date_col)*86400, origin = "1899-12-30", tz = "UTC")
# 提取纯日期部分
df$date_col <- as.Date(df$date_time_col)

第三步：验证转换结果 #

转换后用以下代码检查是否有未识别的NA值，这些通常是输入错误的单元格，需要手动核对：

# 查看转换失败的行
filter(df, is.na(date_col))

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