在R中处理解析错误的混合格式日期时间数据

针对你遇到的Excel导入后date_time列混合了错误解析的数值型日期和文本型日期的问题，可以通过以下步骤在R中统一转换为正确的dttm（POSIXct）对象：

核心思路 #

• 区分两种格式：数值型是Excel错误将dd-mm解析为mm-dd后的日期值，文本型是Excel无法解析（日>12）保留的原始dd-mm-yyyy格式字符串。
• 对数值型：先还原Excel错误解析的日期，再调换日和月得到正确日期；对文本型：直接按dd-mm-yyyy格式解析。

具体代码实现 #

library(lubridate)
library(dplyr)

# 示例数据
date_time <- c(
  45144.4762962963,
  45144.475138888891,
  "14-08-2023 10:38:54 AM",
  "14-08-2023 10:37:03 AM",
  "24-05-2023 9:29:44 AM",
  "24-05-2023 9:27:18 AM",
  45113.462395833332,
  45113.461261574077,
  "15-05-2023 1:47:12 PM",
  44988.399664351855
)

data <- tibble(date_time)

# 定义转换函数
fix_mixed_datetime <- function(x) {
  # 统一转为字符格式，方便判断类型
  x_char <- as.character(x)
  # 标记哪些是数值型的Excel日期
  is_numeric <- !is.na(suppressWarnings(as.numeric(x_char)))
  
  # 处理数值型日期：还原错误解析的日期后调换日和月
  numeric_vals <- as.numeric(x_char[is_numeric])
  # Excel日期原点用1899-12-30（兼容1900年bug），转换为错误的datetime
  wrong_dt <- as.POSIXct(numeric_vals * 86400, origin = "1899-12-30", tz = "UTC")
  # 调换日和月得到正确日期，保留原时间部分
  correct_dt_numeric <- swap(wrong_dt, day, month)
  
  # 处理文本型日期：直接按dd-mm-yyyy hh:mm:ss格式解析
  correct_dt_text <- dmy_hms(x_char[!is_numeric], tz = "UTC")
  
  # 合并两部分结果
  result <- vector("list", length(x_char))
  result[is_numeric] <- as.list(correct_dt_numeric)
  result[!is_numeric] <- as.list(correct_dt_text)
  do.call(c, result)
}

# 应用转换函数
data <- data %>% 
  mutate(correct_datetime = fix_mixed_datetime(date_time))

# 查看转换结果
print(data)

关键细节说明 #

1. 时区设置：代码中用tz = "UTC"，可根据你的需求替换为实际时区（如"Asia/Shanghai"）。
2. Excel日期原点：使用1899-12-30而非1900-01-01，是为了兼容Excel的1900年闰年bug，确保日期数值转换准确。
3. lubridate函数：swap()用于快速调换日和月，dmy_hms()自动识别dd-mm-yyyy格式及AM/PM时间，简化解析逻辑。

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