在macOS上用R的read_excel()读取Windows生成的xlsx文件时日期格式异常求助

问题分析 #

你遇到的情况是因为原Excel文件的Date列存在混合单元格格式：部分单元格是文本型的年份（如1998、2018），部分是Excel原生日期格式（内部以数值存储，起始基准为1899-12-30）。readxl读取时无法自动统一识别，导致列内同时出现年份数值和日期数值。

解决方案 #

方案1：强制以字符格式读取（保留原始内容） #

读取时通过col_types参数指定Date列为文本格式，直接保留单元格的原始内容，后续可按需处理：

library(readxl)
# 假设文件名为data.xlsx，第一列是Company，第二列是Date，需根据实际列数调整col_types的长度
date_data <- read_excel("data.xlsx", col_types = c("text", "text"))

方案2：读取后统一转换为日期格式 #

先读取数据，再针对混合内容分情况转换：

• 对数值≥10000的（Excel日期数值），用as.Date配合origin="1899-12-30"转换
• 对数值<10000的（年份值），补全为当年1月1日的日期格式

代码示例：

library(readxl)
date_data <- read_excel("data.xlsx")

# 处理Date列
date_data$Date <- ifelse(
  date_data$Date >= 10000,
  as.Date(date_data$Date, origin = "1899-12-30"),
  as.Date(paste0(date_data$Date, "-01-01"))
)

如果读取后Date列是字符类型，改用以下代码：

date_data$Date <- sapply(date_data$Date, function(x) {
  num_x <- as.numeric(x)
  if (!is.na(num_x) && num_x >= 10000) {
    as.Date(num_x, origin = "1899-12-30")
  } else {
    as.Date(paste0(x, "-01-01"))
  }
})

方案3：仅保留年份格式（若不需要完整日期） #

如果只需要年份信息，可统一提取年份：

date_data$Year <- sapply(date_data$Date, function(x) {
  num_x <- as.numeric(x)
  if (!is.na(num_x) && num_x >= 10000) {
    format(as.Date(num_x, origin = "1899-12-30"), "%Y")
  } else {
    as.character(x)
  }
})

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