Menu
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?
762
You can use the as. Date( ) function to convert character data to dates. The format is as. Date(x, "format"), where x is the character data and format gives the appropriate format.
To format = , provide a character string (in quotes) that represents the current date format using the special “strptime” abbreviations below. For example, if your character dates are currently in the format “DD/MM/YYYY”, like “24/04/1968”, then you would use format = "%d/%m/%Y" to convert the values into dates.
Note that the default date format is YYYY-MM-DD; therefore, if your string is of different format you must incorporate the format argument. There are multiple formats that dates can be in; for a complete list of formatting code options in R type ? strftime in your console.
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.
200
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
