# 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.