Let's break down what's going wrong here and fix it step by step:

The Two Key Issues #

1. You turned the column into a list (not a numeric vector)
   Using lapply() on a data frame column returns a list, not a numeric vector. When as.Date() tries to process a list, it doesn't behave as expected—this is why all your dates ended up the same.

2. Incorrect as.Date() parameter usage
   Excel stores dates as the number of days since 1899-12-30, but you passed this date as the format argument (which is for parsing text dates). You need to use the origin parameter instead.

Step-by-Step Fix #

First, convert your character-based Excel date numbers to a proper numeric vector:

# If the column is still character type:
all$time_period <- as.numeric(all$time_period)

# If you already used lapply and it's a list, unlist it first:
all$time_period <- as.numeric(unlist(all$time_period))

Then convert to a date and format it:

all$time_period <- format(as.Date(all$time_period, origin = "1899-12-30"), "%Y-%m-%d")

Testing this with your sample values:

sample_dates <- c(41640, 41671, 41699, 41730, 41760, 41791)
format(as.Date(sample_dates, origin = "1899-12-30"), "%Y-%m-%d")
# Output: "2014-01-01" "2014-02-01" "2014-03-01" "2014-04-01" "2014-05-01" "2014-06-01"

Pro Tip: Avoid Manual Conversion Altogether #

Use the readxl package to read your Excel file directly—it automatically recognizes Excel's numeric date format and converts it to R's date type:

library(readxl)
all <- read_excel("your_excel_file.xlsx", col_types = c("guess", "guess", "date"))

This saves you from having to do the conversion manually!

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