rexcel

Imported Excel file with dates, came out as numbers, trying to return to date format

Based on the structure of the data, I could not import the data by defining the types prior to upload/import.

So, doing that won't work. Anyway, it comes out as a number, below for examples:

43683
43686
43689

I did try some methods. Below is the code I did use and it did sort of end up turning it into a date but the year is wrong (comes out 2089 instead of 2019). I did do trimws prior to the below.

Transactions_Combined$Trans_Date <- as.numeric(Transactions_Comvined$Trans_Date)

Transaction_Combined <- as.Date(Transactions_Combined$Trans_Date)

Comes out as:

'2089-08-07'
'2089-08-10'
'2089-08-13'

Just want an accurate date and make sure I'm doing it right too.

Solution

  • Use the function readxl::read_excel() from the readxl-package to read in your excel-data.
    Set the col_types-argument to "Date", and it will read the excel-dates to POSIXct-timestamp.

    If this is not an option, you can try

    as.POSIXct( colum_with_excel_times * 24 * 3600 + as.POSIXct( "1899-12-30 00:00" ) )