How to Convert Excel Date Format to Proper Date in R

---

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