How to convert Excel date format to proper date in R

RExcelDateDatetimeLubridate

R Problem Overview


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?

R Solutions


Solution 1 - R

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.

Solution 2 - R

Here is another way to do it using janitor and tibble packages:

install.packages("janitor")
install.packages("tibble")

library(tibble)
library(janitor)

excel_numeric_to_date(as.numeric(as.character(YourDate)), date_system = "modern")    

Solution 3 - R

openxlsx package also allows xls date conversion:

openxlsx::convertToDate(42705)
[1] "2016-12-01"

And as suggested by @Suren, convertToDateTime allows datetime conversion:

openxlsx::convertToDateTime(42705.5)
[1] "2016-12-01 12:00:00"

Solution 4 - R

As it was said, very good options:

as.Date(42705, origin = "1899-12-30")

openxlsx::convertToDate(42705)

Another way also could be:

format(as.Date(as.Date("1899-12-30") + 42705, "%d-%m-%Y"), "%d-%m-%Y")

Note you can change the output format where it's written %d-%m-%Y

(first of all, convert as.numeric if it's imported as character!,or converting in the formula:

format(as.Date(as.Date("1899-12-30") + as.numeric( number formatted as character), "%d-%m-%Y"), "%d-%m-%Y")

Solution 5 - R

If you work with the data.table package you could use as.IDate() for that:

require(data.table)

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

Works like base::as.Date() here.

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
Questionelksie5000View Question on Stackoverflow
Solution 1 - RAndrew BrēzaView Answer on Stackoverflow
Solution 2 - RReza RahimiView Answer on Stackoverflow
Solution 3 - RWaldiView Answer on Stackoverflow
Solution 4 - RCONSULT FIVView Answer on Stackoverflow
Solution 5 - RandscharView Answer on Stackoverflow