求助：R中自动转换导入为character的Excel日期时间列

问题背景 #

处理固定58列的Excel数据，其中6列是YYYY-MM-DD HH:MM:SS格式的日期时间，Excel显示正常，但导入R后部分列会变成字符型的十进制数字（比如44651.422222222223），且每次异常列不固定。尝试用ymd_hms()转换时出现报错：

Warning message:
All formats failed to parse. No formats found. 

错误原因 #

ymd_hms()是用来解析字符串格式的日期时间（比如"2024-03-21 10:55:00"）的工具，但你遇到的十进制字符串是Excel的日期序列号——它代表从1900年1月0日（实际因Excel的闰年bug，起始点是1899-12-30）开始的天数，小数部分是一天内的时间占比，所以不能直接用ymd_hms()解析。

解决方案 #

• 方案1：修复现有转换代码
  先把字符型的序列号转成数值，再基于Excel的起始日期转换为标准日期时间，最后替换回原数据框：

library(lubridate)
library(tidyverse)

# 你的测试数据
time1 <- ymd_hms(c("2024-03-21 10:55:00", "2024-01-22 04:37:02", "2024-5-3 23:43:09" ))
time2 <- as.character(c("44651.422222222223", "44652.873611111114", "44654.685416666667" ))
time3 <- ymd_hms(c("2024-03-01 12:15:19", "2024-1-18 01:07:20", "2024-5-6 13:33:29" ))
time4 <- ymd_hms(c("2024-11-17 08:05:26", "2024-09-12 10:13:11", "2024-4-3 12:53:30" ))
time5 <- ymd_hms(c("2024-09-19 01:45:42", "2024-11-26 06:00:20", "2024-5-13 22:03:05" ))
time6 <- ymd_hms(c("2024-05-29 03:25:37", "2024-08-02 08:05:28", "2024-12-23 03:13:6" ))
col1 <- c("blah", "blah", "blah")
col2 <- c("blah", "blah", "blah")
col3 <- c("blah", "blah", "blah")

df <- data.frame(time1, time2, time3, time4, time5, time6, col1, col2, col3)

times <- c("time1", "time2", "time3", "time4", "time5", "time6")

# 批量处理异常列
df_fixed <- df %>%
  mutate(across(all_of(times), ~{
    # 判断是否是字符型的Excel日期序列号
    if(is.character(.x) && all(grepl("^\\d+\\.\\d+$", .x))){
      # 转数值后，基于Excel起始日期转换为日期时间
      as.POSIXct(as.numeric(.x)*86400, origin = "1899-12-30", tz = "UTC")
    } else {
      # 原本正常的日期时间列保持不变
      .x
    }
  }))

# 查看转换结果
str(df_fixed)

• 方案2：从导入环节避免问题
  既然列名和格式固定，导入时直接指定日期列的类型，用readxl包的read_excel()函数可以精准控制：

library(readxl)

# 定义所有列的类型：先默认设为"guess"，再把6个时间列设为"date"
col_types <- rep("guess", 58)
time_cols <- c("time1", "time2", "time3", "time4", "time5", "time6")
col_types[match(time_cols, colnames(read_excel("your_file.xlsx", n_max = 0)))] <- "date"

# 导入数据
df <- read_excel("your_file.xlsx", col_types = col_types)

这样导入时会直接把指定列解析为日期时间类型，避免后续转换的麻烦。

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