在R中转换read_excel读入的混合格式日期时间为dttm对象

可以通过区分格式类型分别转换的方式解决这个问题，利用lubridate包处理Excel日期序列号和字符串日期，最终统一为dttm（即POSIXct）类型。

步骤说明 #

1. 识别两种格式：判断每个元素是Excel日期序列号（可转换为数值）还是字符串格式日期
2. 分别转换：使用lubridate的专用函数处理两类格式
3. 合并结果：将转换后的结果整合成统一的日期时间列

完整代码示例 #

# 加载所需包
library(tidyverse)
library(lubridate)

# 示例数据
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)

# 自定义转换函数
convert_mixed_datetime <- function(x) {
  # 标记Excel序列号（能转为数值的元素）
  is_excel <- !is.na(suppressWarnings(as.numeric(x)))
  
  # 初始化结果向量
  result <- vector("POSIXct", length(x))
  
  # 转换Excel序列号：保留时间部分
  result[is_excel] <- excel_numeric_to_date(as.numeric(x[is_excel]), include_time = TRUE)
  
  # 转换字符串日期：识别日-月-年 + 时:分:秒 + 上/下午格式
  result[!is_excel] <- dmy_hms(x[!is_excel])
  
  # 转为lubridate的dttm类型
  as_datetime(result)
}

# 应用转换并生成新列
data <- data %>%
  mutate(clean_datetime = convert_mixed_datetime(date_time))

# 查看转换结果
print(data)

关键函数解释 #

• suppressWarnings(as.numeric(x))：静默尝试将字符转为数值，用于区分Excel序列号和字符串日期，避免转换失败的警告干扰
• excel_numeric_to_date(..., include_time = TRUE)：将Excel日期序列号（含小数时间部分）直接转为带时间的日期对象
• dmy_hms()：自动识别日-月-年 时:分:秒 上/下午格式的字符串，转为日期时间对象
• as_datetime()：确保最终结果为lubridate定义的dttm类型（本质是POSIXct的别名，兼容所有日期时间操作）

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