Convert multiple colums from Excel number to date format

Question

I downloaded an Excel file with 200+ columns. Some are character, some numeric and other date format. The date columns are downloaded in R as an Excel number format like "42369" "43380".

B_1	B_2	B_Date_1	B_Date_2	V_1	V-2	V_3	Z_1	Z_2	Z_3	V_Date_1	V_Date_2	V_Date_3
NULL	qsdsq	42369	43380	5.6	7.2	2.3	dsff	fdgsfd	fdgs	42370	42380	42369
afdf	dsqfs	42452	43383	5.1	2.8	3.7	hgfj	azer	NULL	42370	42380	42369

I found the

excel_numeric_to_date()

command from the library(janitor) to convert one date column from Excel number to date format.

df$B_Date_1 <- as.numeric(df$B_Date_1)
df$B_Date_1 <- excel_numeric_to_date(df$B_Date_1)

I would like to repeat this command for all the date columns (there are many). I selected the column names

select = c(B_Date_1:B_Date2,V_Date_1:V_Date_2)

and tried various approaches with lapply()or with mutate() like

df %>% mutate(across(subset(select = c(B_Date_1:B_Date_2,V_Date_1:V_Date_3)),
       as.numeric())) %>% 
       excel_numeric_to_date()

But could not find any solution to convert all the excel number date column into a Date format column in R.

Answer 1

The correct syntax would be

df %>% 
  mutate(across(c(B_Date_1:B_Date_2,V_Date_1:V_Date_3), excel_numeric_to_date))

That returns

   B_1   B_2   B_Date_1   B_Date_2 V_1 V.2 V_3  Z_1    Z_2  Z_3   V_Date_1   V_Date_2
1 NULL qsdsq 2015-12-31 2018-10-07 5.6 7.2 2.3 dsff fdgsfd fdgs 2016-01-01 2016-01-11
2 afdf dsqfs 2016-03-23 2018-10-10 5.1 2.8 3.7 hgfj   azer NULL 2016-01-01 2016-01-11
    V_Date_3
1 2015-12-31
2 2015-12-31

If you want all columsn with the word "Date" in them, you could also do

df %>% mutate(across(contains("Date"), excel_numeric_to_date))