How to convert Excel date format to proper date in R

r date datetime lubridate
elksie5000 picture elksie5000 · Apr 5, 2017 · Viewed 69.8k times · Source

I'm working with a csv which unfortunately has logged datetimes using the number format of 42705 although it should be 01/12/2016.

I'd like to convert it to the right format in R using lubridate or some other package. Is there a function that will handle it?

Answer

Andrew Brēza picture Andrew Brēza · Apr 5, 2017

You don't need to use lubridate for this, the base function as.Date handles this type of conversion nicely. The trick is that you have to provide the origin, which in Excel is December 30, 1899.

as.Date(42705, origin = "1899-12-30")
# [1] "2016-12-01"

If you want to preserve your column types, you can try using the read_excel function from the readxl package. That lets you load an XLS or XLSX file with the number formatting preserved.

Related questions

How to prevent ifelse() from turning Date objects into numeric objects

I am using the function ifelse() to manipulate a date vector. I expected the result to be of class Date, and was surprised to get a numeric vector instead. Here is an example: dates <- as.Date(c('2011…

Read More
Fastest way to parse a date-time string to class Date

I have a column with dates as character in the format 10/17/2017 12:00:00 AM. I want parse the string and keep only the date part as class Date, i.e. 2017-10-17. I am using - df$ReportDate = as.Date(df$ReportDate, …

Read More
How to calculate number of days between two dates in R

I'm trying to subtract two dates in R. These are the two dates via the structure command: str(standard_data_4testing$start_datetime) POSIXct[1:489124], format: "2016-02-01 00:38:49" "2016-02-01 07:48:53" "2016-02-01 08:32:08" "2016-02-01 11:21:13" ... str(standard_data_4testing$original_installdate) Date[1:489124], …

Read More