1

I have this column is date of birth. Is there any R function or any method to solve this date problem, the dates in Excel are ok. But when I read the file in R I am getting wrong dates as below:

2047-11-03

2064-06-08

1098639-10-01

377383-01-18

1979098-01-27

-177103-09-27

-186092-08-24

1612569600

-236822400

the original dates in excel as below

01/11/1977

07/06/1994

11/09/1982

07/05/1974

19/11/1992

06/12/1967

29/10/1967

06/02/2021

01/07/1962

I used the following to imports the data df<- import_list("df.xlsx")

Abdulsalam Elnaeem
  • 83
  • 5
  • 3
    Can you post the true dates corresponding to those values in the question, please? How are you reading the files? Please post the code producing those results. And see if [this SO post](https://stackoverflow.com/questions/43230470/how-to-convert-excel-date-format-to-proper-date-in-r) solves the problem. – Rui Barradas Apr 07 '22 at 05:14
  • 1
    Could you try to import this data table with either the command `df <- openxlsx::read.xlsx("df.xlsx")` or `df <- readxl::read_excel("df.xlsx")`? – Vincent Guillemot Apr 07 '22 at 06:00
  • This seems like your "ok dates" in excel are in a weird format, perhaps they are only okay on the surface, but not at cell level. – Oliver Apr 07 '22 at 06:07
  • Please note that my data is multiple excel sheets, that why i used the import_list () – Abdulsalam Elnaeem Apr 07 '22 at 06:25
  • please add a sample excel file to make the problem reproducible.. – Wimpel Apr 07 '22 at 07:25
  • `readxl::read_xlsx()` didn't seem to have any difficulty in parsing the dates. You can read a list of files by mapping the list onto read_xlsx. Something like `map(file_list, read_xlsx)` – mkpt_uk Apr 07 '22 at 07:34
  • Thanks for all, the problem solved: the problem was in reading using import_list(). i used the following: sheets <- readxl::excel_sheets(path) df <- lapply(sheets,readxl::read_excel, path=path) names(df) <- sheets – Abdulsalam Elnaeem Apr 07 '22 at 09:17
  • That seems surprising to me. I think `rio::import_list('x.xlsx')` actually calls `readxl::read_xlsx('x.xslx')` under the hood so I would have expected the same results. But glad you solved your problem anyhow. – Dan Adams Apr 07 '22 at 13:11

0 Answers0