R读取Excel数值被识别为DateTime格式问题及格式转换求助

Hey there, let's break down why this is happening and how to fix it—this is a super common gotcha with Excel and R!

The Root Cause #

Excel stores dates as serial numbers (counting days since January 1, 1900 for Windows-based Excel). Your "custom formatted numeric values" are actually these date serial numbers—Excel just uses a custom display format to make them look like regular numbers.

When readxl reads your file, it looks at the underlying cell data type (date/serial) instead of the visible format, so it parses those cells as DateTime objects. When you run as.numeric() on a DateTime object in R, you get the number of seconds since the Unix epoch (January 1, 1970)—that's why you're seeing those huge, unrelated values instead of the numbers you expect.

Solutions to Fix This #

1. Convert DateTime Values to Excel Serial Numbers in R

Instead of converting directly to numeric, we can translate the R DateTime objects back to Excel's serial number format (which matches what you see when switching Excel to General format). Here's how to adjust your loop:

library(readxl)
library(lubridate) # Makes date handling easier

l <- as.data.frame(read_excel(choose.files(), sheet=2, range="C6:BG123"))
vec1 <- c("t", rep("n", ncol(l)-1))

for(j in 1:length(vec1)){
  if(vec1[j] == "t"){
    l[,j] <- as.character(l[,j])
  } else if(vec1[j] == "n"){
    # Check if the column was parsed as DateTime
    if(inherits(l[,j], c("POSIXct", "Date"))){
      # Calculate Excel's epoch (1900-01-01)
      excel_epoch <- ymd("1900-01-01")
      # Add 1 to account for Excel's 1900 leap year bug
      l[,j] <- as.numeric(l[,j] - excel_epoch) + 1
    } else {
      l[,j] <- as.numeric(l[,j])
    }
  }
}

2. Force readxl to Parse Columns as Numeric on Import

You can use the col_types argument in read_excel() to tell R exactly how to parse each column, ignoring Excel's underlying cell type. Since your first column is text and the rest are numeric, define the types upfront:

library(readxl)

# First, get the number of columns to set up col_types
sample_read <- read_excel(choose.files(), sheet=2, range="C6:BG123", n_max=0)
col_types <- c("text", rep("numeric", ncol(sample_read) - 1))

# Now read the data with specified column types
l <- as.data.frame(read_excel(choose.files(), sheet=2, range="C6:BG123", col_types = col_types))

This will parse the columns exactly as you need them, no post-processing required.

3. Adjust Excel Format Before Import (If You Prefer)

If you want to fix the Excel file first:

• Open the file and select the columns with custom formatted values
• Go to the Home tab, click the dropdown in the Number Format menu, and select General
• Save the file, then import it into R as normal.

This will make Excel's display format match the underlying serial number, so readxl will automatically parse them as numeric values.

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