Converting text date to date

[ply]

Hi
I read an excel file using readxl read_xlsx and the first column of the spreadhseet is a text data
e.g. "01/01/2022"
The class of the column created in the dataframe is character :-

Date entry created
"character"

and the values are as expected
Date entry created

1 43913
2 43929
3 43923

but when I try the as.date function to convert the column it keeps throwing an error

data[1] <- as.Date(data[1],origin="1899-01-01")

do not know how to convert 'data[1]' to class “Date”

What daft thing am I doing wrong

[rdr]

Note that:

> as.Date("43913", origin = "1899-12-30")
Error in charToDate(x) : 
  character string is not in a standard unambiguous format

but:

> as.Date(43913, origin = "1899-12-30")
[1] "2020-03-23"

[FJCC]

I'd like to mention a couple of details about converting date values from Excel into dates in R. The origin argument of as.Date designates the date that has the value 0. That is 1899-12-31 in Excel. However, Excel also accepts 1900-02-29 as a valid date though 1900 was not actually a leap year. For date values after 1900-02-28, you have to use 1899-12-30 as the origin in as.Date to get the correct date in R. A date value of 43913 represents March 23, 2020 in Excel.

as.Date(43913,origin="1899-12-30")
[1] "2020-03-23"

If you need to convert characters to dates, you can use code like

DF$Date <- as.Date(DF$Date, format = "%d/%m/%Y")

where the format argument shows the format of the imported characters, not the format of function output. The lubridate package has handy function for converting characters to dates. If the characters are in Day-Month-Year format

DF$Date <- lubridate::dmy(DF$Date)

[rdr]

Good points. I adjusted my examples to show the correct origin. I don't use Excel generally, so I wasn't aware of the different origin arguments. Indeed, it appears there are differences in Excel even between platform implementations. Seems a mess. My point to the OP, however, is that a character vector passed to as.Date returns an error whereas a numeric argument returns a Date value.

[ply]

Really sorry still not working

Df has say 10 columns in , columns 1 and 4 are in excel dates but text format so if I look at their class in R they say character and are for example 43913

How do I change columns 1 and 4 to date format for dataframe DF

[ply]

The column names are long e.g. "Date entry created"

[ply]

used makenames to change the column names to make it easier then tried this

new_dataframe <- df %>% mutate(Date.entry.created=as.Date(Date.entry.created, origin = "1899-12-30"))

got error
Error: Problem with mutate() column Date.entry.created.
Date.entry.created = as.Date(Date.entry.created, origin = "1899-12-30").
x character string is not in a standard unambiguous format

if I change the line to
new_dataframe <- data %>% mutate(Date.entry.created=as.Date(Date.entry.created, format="%d/%m/%Y"))... an dother formats. It doesnt error but the first column instead of showing dates just shows NA

[nirgrahamuk]

as.Date("43913", origin = "1899-12-30")
Error in charToDate(x) : 
  character string is not in a standard unambiguous format

 as.Date(as.numeric("43913"), origin = "1899-12-30")
[1] "2020-03-23"