-1

Actual data in the spreadsheet is as below. I have removed other columns to save space.

EndTime
7/15/2020 9:34:03 AM
8/5/2020 3:19:10 PM
8/10/2020 9:52:58 AM
8/17/2020 8:07:14 PM
9/8/2020 6:11:52 AM

#Reading the spreadsheet "File_Name" from "Report" sheet readSheet<-read_excel("File_Name", "Report")

when I do head(readSheet) , I am getting the following with other columns which is removed here.

EndTime
<chr>
44027.39795~
44023.34152~
44045.35236~
44053.44812~

When I change the column format from Date to Generic in the excelsheet, I get the same values as above. I have also used the col_names and col_types for the above column as "date" but didnt help. I have tried as.Date(), convertToDate(), anydate() and other functions to change the format of date in the dataframe but was not able to convert the format in the read dataframe.

misran
  • 37
  • 2
  • Are your date fields in the Excel sheet all in the same format (one quick way to check this is whether some fields are left-aligned and others right-aligned (or vice versa)). – R.W. Jan 04 '22 at 10:51
  • Thanks for the response. Left aligned the entire date column and getting this error Error in charToDate(x) : character string is not in a standard unambiguous format – misran Jan 04 '22 at 11:11

1 Answers1

1

Hard to say without the exact code/sample data, or errors you're encountering (if applicable), but usually as.Date() should work e.g:

dataframe$date_field = as.Date(dataframe$date_field)
thehand0
  • 1,123
  • 4
  • 14
  • readSheet<-read_excel(File_Name, "SheetReport") as.Date(readSheet$EndTime, origin = "1899-12-30") Getting the below error Error in charToDate(x) : character string is not in a standard unambiguous format The date is in the format 2020-07-15. – misran Jan 04 '22 at 11:42
  • With the error message you're presenting, the problem would seem to be that some date in the Excel sheet is not formatted on the "YYYY-MM-DD" format, i.e. the formatting isn't consistent across the relevant column - can you check this? – R.W. Jan 04 '22 at 12:07
  • I actually changed the column format from type 14-Mar-12 in English(United States) to the format 2012-03-14 in English(United Kingdom) and have filtered some of the NULL values in the column before applying as.Data() but still getting the error. – misran Jan 04 '22 at 12:20
  • 1
    How did you change the columns? Perhaps on the original you can try on the orignal data, `dataframe$date_field = as.Date(dataframe$date_field, format='%d %b %Y')` – thehand0 Jan 04 '22 at 14:26