You can use the following methods to convert Excel dates that are formatted as numbers into proper dates in R:
Method 1: Convert Excel Number to Proper Date in R
df$date Date(df$date, origin = "1899-12-30")
Method 2: Convert Excel Number to Proper Datetime in R
library(openxlsx)
df$datetime
The following examples show how to use each method in practice with an Excel file called sales_data.xlsx that contains the following data:
Example 1: Convert Excel Number to Proper Date in R
The following code shows how to use the as.Date() function in base R to convert the numeric values in the date column of the Excel file into proper dates in R:
library(readxl) #import Excel file into R as data frame df C:\Users\bob\Documents\sales_data.xlsx") #view data frame df # A tibble: 10 x 3 date datetime sales 1 44563 44563. 14 2 44566 44567. 19 3 44635 44636. 22 4 44670 44670. 29 5 44706 44706. 24 6 44716 44716. 25 7 44761 44761. 25 8 44782 44782. 30 9 44864 44864. 35 10 44919 44920. 28 #convert Excel number format to proper R date df$date Date(df$date, origin = "1899-12-30") #view updated data frame df # A tibble: 10 x 3 date datetime sales 1 2022-01-02 44563. 14 2 2022-01-05 44567. 19 3 2022-03-15 44636. 22 4 2022-04-19 44670. 29 5 2022-05-25 44706. 24 6 2022-06-04 44716. 25 7 2022-07-19 44761. 25 8 2022-08-09 44782. 30 9 2022-10-30 44864. 35 10 2022-12-24 44920. 28
Notice that the values in the date column are now formatted as proper dates.
Example 2: Convert Excel Number to Proper Datetime in R
The following code shows how to use the convertToDateTime() function from the openxlsx package in R to convert the numeric values in the datetime column of the Excel file into proper datetimes in R:
library(readxl) library(openxlsx) #import Excel file into R as data frame df C:\Users\bob\Documents\sales_data.xlsx") #view data frame df # A tibble: 10 x 3 date datetime sales 1 44563 44563. 14 2 44566 44567. 19 3 44635 44636. 22 4 44670 44670. 29 5 44706 44706. 24 6 44716 44716. 25 7 44761 44761. 25 8 44782 44782. 30 9 44864 44864. 35 10 44919 44920. 28 #convert Excel datetime to proper datetime in R df$datetime #view updated data frame df # A tibble: 10 x 3 date datetime sales 1 44563 2022-01-02 04:14:00 14 2 44566 2022-01-05 12:15:00 19 3 44635 2022-03-15 15:34:00 22 4 44670 2022-04-19 09:45:00 29 5 44706 2022-05-25 10:30:00 24 6 44716 2022-06-04 10:15:00 25 7 44761 2022-07-19 01:13:00 25 8 44782 2022-08-09 02:15:00 30 9 44864 2022-10-30 04:34:00 35 10 44919 2022-12-24 21:23:00 28
Notice that the values in the datetime column are now formatted as proper dates.
Note: You can also use the convertToDate() function from the openxlsx package to convert a numeric date to a proper date in R.
Additional Resources
The following tutorials explain how to perform other common tasks in R:
How to Import Excel Files into R
How to Export Data Frame to an Excel File in R
How to Export Data Frames to Multiple Excel Sheets in R