使用R语言xlsx/readxl包如何正确批量读取Excel日期时间列

出现混杂输出的核心原因是Excel目标列的单元格格式未统一：

• 部分单元格是Excel原生日期格式，底层存储为距离1900-01-01的天数序列号（即输出里的44293、44294这类数值）
• 其余单元格是手动输入的纯文本格式日期，未被Excel识别为日期类型，最终导致读取后同一列混合存储数值、文本两类内容

xlsx包读取时会自动把混杂类型列转成因子，因此会出现空值层级、文本和数值混杂的因子输出；readxl包读取时会自动推断列类型，遇到混杂内容就会统一转成字符串，两种方式都没有完成日期格式的标准转换。

读取时的前置设置 #

批量场景优先使用readxl包读取，不要使用xlsx包——后者对混杂格式列的类型处理更粗糙，很难后续批量校正。
读取所有文件时提前设置全列按文本读，避免包的自动类型推断打乱原始内容：

# 首次运行先安装依赖
# install.packages(c("readxl", "lubridate", "dplyr"))
library(readxl)
library(lubridate)
library(dplyr)

# 读取单文件示例：强制所有列按文本读，不做自动类型转换
raw_df <- read_excel("file.xlsx", col_types = "text")

编写通用的混杂日期转换函数 #

不需要手动修改单个Excel文件，对读进来的文本列做统一规则转换即可适配全部同类型文件：

parse_mixed_date <- function(x) {
  # 去除所有值的前后空白
  x <- trimws(as.character(x))
  # 识别纯数字的Excel序列号项
  serial_pos <- grepl("^\\d+$", x)
  # 识别文本格式的日期项
  text_pos <- !serial_pos
  # 序列号转标准日期：用1899-12-30做origin是为了校准Excel自带的1900年闰年识别bug，不会出现日期偏差
  x[serial_pos] <- as.Date(as.numeric(x[serial_pos]), origin = "1899-12-30")
  # 解析文本格式的日/月/年型日期
  x[text_pos] <- dmy(x[text_pos])
  # 统一转成标准Date类型输出
  as.Date(x)
}

针对同样格式混乱的时间列，可复用相同逻辑编写转换函数：

parse_mixed_time <- function(x) {
  x <- trimws(as.character(x))
  serial_pos <- grepl("^\\d+\\.?\\d*$", x) # 识别时间序列号（0-1的小数代表一天的占比）
  text_pos <- !serial_pos
  # 时间序列号转hms类型
  x[serial_pos] <- as.numeric(x[serial_pos]) * 86400 # 一天按秒算的总时长
  x[serial_pos] <- as_hms(as.numeric(x[serial_pos]))
  # 解析文本格式的时间
  x[text_pos] <- hms(x[text_pos])
  as_hms(x)
}

批量应用转换 #

对目录下所有同类型xlsx文件循环应用转换逻辑，一次处理全部文件：

# 获取当前目录下所有xlsx文件路径
all_files <- list.files(pattern = "\\.xlsx$", full.names = TRUE)
# 批量读取并清洗
full_dataset <- lapply(all_files, function(current_file) {
  df <- read_excel(current_file, col_types = "text")
  # 对指定的日期、时间列应用转换函数
  df$`Date.pub` <- parse_mixed_date(df$`Date.pub`)
  # df$`Time.col` <- parse_mixed_time(df$`Time.col`) # 替换成实际的时间列名即可
  return(df)
}) |> bind_rows()

• 文本日期的解析格式要和Excel里的实际格式匹配，比如如果文本是月/日/年格式，就把dmy()换成mdy()即可
• 转换完成后可以用summary(full_dataset$Date.pub)检查转换结果是否存在NA值，若有少量NA可通过parse_date_time()做更宽松的格式匹配
• 整个流程完全不需要手动打开每个Excel调整单元格格式，可适配同结构的批量文件

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