0

I did the following code to import a excel file in Rstudio:

(nms <- names(read_excel("myexcelfile.xlsx")))
(ct <- ifelse(grepl("^Date", nms), "text", "numeric"))
read_excel("myexcelfile.xlsx", col_types = ct)[-c(6:495),-c(3:71)]

The resul is this dataframe below:

structure(list(Data = c("41731", "41730", "41729", "41726", "41725"
), ABEV3 = c(15.2, 14.9, 15.22, 15.15, 15.18)), row.names = c(NA, 
-5L), class = c("tbl_df", "tbl", "data.frame"))

The first column should be the dates (Brazilian Format: Day/Month/Year).

enter image description here

How can I fix this?

Improve this question

1 Answer 1

Reset to default
1

Try this:

(nms <- names(read_excel("myexcelfile.xlsx")))
(ct <- ifelse(grepl("^Date", nms), "date", "numeric"))
df <- read_excel("myexcelfile.xlsx", col_types = ct)[-c(6:495),-c(3:71)]

df$Date <- format(as.Date(as.character(df$Date)), "%d-%m-%Y")

Instead of importing as text, import it as date and then format it in the way described below (there I suppose that you will save the Excel in the df dataframe and that the column will be called Date, but adjust as needed).

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

It doesnt work: I have this message: Error in charToDate(x) : string de caracteres não é um formato padrão não ambíguo
I set arbitrarily: as.Date(df$Date,origin = "1899-12-30") and it works. Why?
This is the origin used by Excel. However I'm not sure why the approach above didn't work straight away, perhaps this is linked to different time zones.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.