Taming Excel Dates in R: From Numbers to Meaningful Dates!
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.
Introduction
Have you ever battled with Excel’s quirky date formats in your R projects? If so, you’re not alone! Those cryptic numbers can be a real headache, but fear not, fellow R warriors! Today, we’ll conquer this challenge and transform those numbers into beautiful, usable dates.
Our Mission: We’ll convert two date columns in a tibble named “df”:
date
: Stored as numbers, representing days since some mysterious date.datetime
: Also in numberland, but with an additional decimal for time.
Our Weapons:
as.Date()
: This built-in R function is our date-conversion hero, but we need to give it a secret weapon:origin = "1899-12-30"
. This tellsas.Date()
where the Excel date system starts counting days from.openxlsx
library: This package helps us deal with Excel files. We’ll use itsconvertToDateTime()
function to handle thedatetime
column, which includes both date and time information.
Let’s Code!
# Install and load the openxlsx library (if needed) if (!require(openxlsx)) install.packages("openxlsx") library(openxlsx) # Our example data df <- data.frame( date = c(44563, 44566, 44635, 44670, 44706, 44716, 44761, 44782, 44864, 44919), datetime = c(44563.17, 44566.51, 44635.64, 44670.40, 44706.43, 44716.42, 44761.05, 44782.09, 44864.19, 44919.89), sales = c(14, 19, 22, 29, 24, 25, 25, 30, 35, 28) ) df
date datetime sales 1 44563 44563.17 14 2 44566 44566.51 19 3 44635 44635.64 22 4 44670 44670.40 29 5 44706 44706.43 24 6 44716 44716.42 25 7 44761 44761.05 25 8 44782 44782.09 30 9 44864 44864.19 35 10 44919 44919.89 28
# Convert "date" column using as.Date() and the magic origin df$date <- as.Date(df$date, origin = "1899-12-30") # Convert "datetime" column using openxlsx and convertToDateTime() df$datetime <- convertToDateTime(df$datetime)
Breaking it Down
- The first line checks if
openxlsx
is installed and loads it if needed. - We create our sample data frame
df
with thedate
anddatetime
columns. - The magic happens! We use
as.Date()
ondf$date
, specifying theorigin
as “1899-12-30”. This tells R to interpret the numbers as days since that date. - For
df$datetime
, we useconvertToDateTime()
from theopenxlsx
package. This function handles both date and time information stored as decimals.
Voila! Our df
now has proper date and datetime columns, ready for further analysis and visualization. Let’s see the results:
head(df, 1)
date datetime sales 1 2022-01-02 2022-01-02 04:04:48 14
You’re Turn!
Now it’s your turn! Grab your own Excel data with mysterious date formats and try this code. Play with different origin
values if needed (depending on your Excel version). Remember, R is a playground, so have fun exploring and taming those dates!
Bonus Tip: Want to format your dates for readability? Use the format()
function, like this:
df$date <- format(df$date, "%d/%m/%Y") df
date datetime sales 1 02/01/2022 2022-01-02 04:04:48 14 2 05/01/2022 2022-01-05 12:14:24 19 3 15/03/2022 2022-03-15 15:21:36 22 4 19/04/2022 2022-04-19 09:36:00 29 5 25/05/2022 2022-05-25 10:19:12 24 6 04/06/2022 2022-06-04 10:04:48 25 7 19/07/2022 2022-07-19 01:12:00 25 8 09/08/2022 2022-08-09 02:09:36 30 9 30/10/2022 2022-10-30 04:33:36 35 10 24/12/2022 2022-12-24 21:21:36 28
This will display your dates in the familiar “day/month/year” format.
So there you have it, fellow R enthusiasts! With these tools, you can confidently handle Excel’s date quirks and unleash the power of your data. Happy coding!
R-bloggers.com offers daily e-mail updates about R news and tutorials about learning R and many other topics. Click here if you're looking to post or find an R/data-science job.
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.