136

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?

Waldi
  • 39,242
  • 6
  • 30
  • 78
elksie5000
  • 7,084
  • 12
  • 57
  • 87

6 Answers6

235

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.

EDIT: Relevant XKCD

XKCD comic strip

Andrew Brēza
  • 7,705
  • 3
  • 34
  • 40
  • 2
    Thank you for that. I'm still very new to R. It worked a treat. – elksie5000 Apr 05 '17 at 11:58
  • Quite welcome. I'm editing my response now since I realized the origin is slightly off. Give me a minute before you use this code in your project :-) – Andrew Brēza Apr 05 '17 at 11:59
  • Actually I just typed 01/12/2016 into Excel and converted it into a number. The value I got was 42381, not 42705. Using 42381 in the `as.Date` function gets you back to 1/12/2016. Are you sure that you copied the number correctly from Excel? – Andrew Brēza Apr 05 '17 at 12:01
  • Oh, I did exactly the same thing in my copy of Excel, but had a different number. Is because it's European day-month-year format? – elksie5000 Apr 05 '17 at 12:05
  • That's exactly what happened, I should have asked what date format you were using. It's still early here in the states and I'm obviously not thinking clearly yet. – Andrew Brēza Apr 05 '17 at 12:07
  • Hey, I just appreciated your swift response. Thank you. – elksie5000 Apr 05 '17 at 12:08
  • This is my favorite solution, but one caution mentioned in the documentation for the "as.Date" function is the handling of leap years can vary between programs, e.g. Excel intentionally misclassifies 1900 as a leap year. If your dates cover broad range, double check the conversion for the min and max dates in your range. – Robert Alan Greevy Jr PhD Apr 28 '20 at 14:31
  • 4
    In the French version of excel, the best solution is quite fascinating, for a date with hours: `as_datetime(Date,origin = "1969-12-31 24:00:00")` – Clément LVD May 31 '20 at 16:54
  • 4
    For those who wonder where the 1899-12-30 came from and if it is correct, it seems in some Excel versions Microsoft has used 1900 as the reference date and in some versions 1904! for this reason I suggest you to confirm the date from some other source as well. Source: https://support.microsoft.com/en-us/office/date-systems-in-excel-e7fe7167-48a9-4b96-bb53-5612a800b487 – Mehrad Mahmoudian Oct 08 '21 at 13:37
  • What parameter do you send? The following keeps returning an error: > as.Date(unlist(table$dates), origin = "1899-12-30") Error in charToDate(x) : character string is not in a standard unambiguous format – tbop Oct 12 '22 at 21:52
  • @tbop Your code works for me using this demo table: table <- tibble(dates = 42705:53400) – Andrew Brēza Oct 14 '22 at 14:10
43

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")
Rui Barradas
  • 70,273
  • 8
  • 34
  • 66
Reza Rahimi
  • 583
  • 6
  • 6
  • 4
    convert_to_date() - also from the janitor package - can be used when there is a mix of Excel numeric dates and actual dates – userLL Aug 11 '21 at 05:17
21

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"
Waldi
  • 39,242
  • 6
  • 30
  • 78
  • 6
    `convertToDateTime` if it is datetime. – kangaroo_cliff Jul 28 '20 at 22:20
  • For some reason, this function only converts to date, not datetime, even though I am providing decimal numbers in my data frame. The function(s) from "janitor" work well and I don't have the same problem with them. – K Bro Jan 25 '23 at 23:10
  • @K bro, tested again above example, still converts to `datetime` on R 4.2 – Waldi Jan 26 '23 at 06:11
3

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")
Martin Gal
  • 16,640
  • 5
  • 21
  • 39
CONSULT FIV
  • 46
  • 2
2

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.

andschar
  • 3,504
  • 2
  • 27
  • 35
0

In line with the Janitor solution provided by Reza, if you have a mix of Excel numeric dates and incorrectly formatted dmy dates, this will work:

df$Procedure.Date <- convert_to_date(df$Procedure.Date, character_fun = lubridate::dmy, string_conversion_failure = "warning")

The character_fun determines the format of date entries, the string_conversion_failure argument specifies that you'll get a warning but the conversion will proceed and saved in your data.

Tom
  • 1
  • 1