Table of Contents
In R, dates stored in Excel are read as numerical values. To convert these values to a proper date format in R, the as.Date() function can be used along with the origin argument. This argument must be set to the date from which the numerical values stored in Excel should begin counting. For example, if the numerical values stored in Excel are days since January 1, 1990, the origin argument should be set to “1990-01-01”. The numerical values can then be passed as the argument to the as.Date() function, and the result will be a proper date format.
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 <- as.Date(df$date, origin = "1899-12-30")
Method 2: Convert Excel Number to Proper Datetime in R
library(openxlsx)
df$datetime <- convertToDateTime(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 <- read_excel("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 <- as.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 <- read_excel("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 <- convertToDateTime(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.