How to convert Excel date format to proper date in R
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.
Reformat Excel numeric date to R date
In excel, number 40182 gives the date 04-01-2010 when formated as date.
So I think you are looking for this:
format(as.Date(40182, origin = "1899-12-30"), '%b-%Y')
which gives:
[1] "Jan-2010"
hope it helps!!!
Converting numbers back to Date object in R
You can use openxlsx
package to convert number to date like
library(openxlsx)
convertToDate("44025")
Or to convert the whole column you can use
convertToDate(df$date)
How to convert in Date format the columns of a particular excel file?
You have to specify the col_types correctly in the read_excel
(or read_xlsx) command. For example:
dataset <- read_xlsx("Data.xlsx",
col_types=c("numeric","date","numeric","date","numeric", "date", ...))
Edit: Finally after much interrogation, the problem is that your data starts in row 3, not 2. So skip the first row (skip=1
) and try again.
dataset <- read_xlsx("Data.xlsx", skip=1)
Convert Excel numeric dates to R dates, but in some rows there is only the year given, e.g. 2018 instead of 43465
Building off of @Ronak's answer, you can use regex to determine a four digit numerical number, then pad with four trailing zeros.
x <- c(NA,NA,43465,43465,43465,43465,2018,NA,43465,43465, 43465, 43465)
ifelse(grepl('^\\d{4}$', x, perl = TRUE),
as.integer(paste0(x, '0000')),
as.integer(format(as.Date(x, origin='1899-12-30'), '%Y%m%d')))
[1] NA NA 20181231 20181231 20181231 20181231 20180000 NA 20181231 20181231 20181231 20181231
You'll get some warning messages regarding the NA
s, and if it bothers you you can add an additional ifelse
to control the NAs. Here we use a logical grep test to see if there are only four numbers (a year), then we create an integer of the values. This allows you to still use mathematical operators such as >
,<
,==
, etc and preserve all the information.
You can change the '0000'
during the paste0()
call to a more appropriate number based upon the data or use case.
Converting excel DateTime serial number to R DateTime
Your number is counting days. Convert to seconds, and you're all set (less a rounding error)
helpData[["ExcelDate"]] <-
as.POSIXct(helpData[["ExcelNum"]] * (60*60*24)
, origin="1899-12-30"
, tz="GMT")
# ID DateTime ExcelNum ExcelDate
# 1 1 3/4/2011 6:00 40606.25 2011-03-04 06:00:00
# 2 2 3/11/2011 7:55 40613.33 2011-03-11 07:54:59
# 3 3 3/13/2011 7:55 40615.33 2011-03-13 07:54:59
# 4 4 3/14/2011 0:00 40616.00 2011-03-14 00:00:00
# 5 5 3/14/2011 10:04 40616.42 2011-03-14 10:03:59
# 6 6 3/14/2011 7:55 40616.33 2011-03-14 07:54:59
# 7 7 3/15/2011 19:55 40617.83 2011-03-15 19:54:59
# 8 8 3/17/2011 7:55 40619.33 2011-03-17 07:54:59
# 9 9 3/18/2011 4:04 40620.17 2011-03-18 04:03:59
# 10 10 3/18/2011 4:04 40620.17 2011-03-18 04:03:59
Excel date format not imported correctly into R
Try using the col_types
parameter
data_corpus <- read_excel("data.xlsx", sheet= "xyz", col_types = c("text", "date"))
Related Topics
Order a "Mixed" Vector (Numbers With Letters)
Force R Not to Use Exponential Notation (E.G. E+10)
How to Arrange a Variable List of Plots Using Grid.Arrange
Select the Top N Values by Group
Generating All Distinct Permutations of a List in R
How to Plot Two Histograms Together in R
Ggplot Does Not Work If It Is Inside a For Loop Although It Works Outside of It
Splitting a Dataframe String Column into Multiple Different Columns
Split Character Column into Several Binary (0/1) Columns
Get the Difference Between Dates in Terms of Weeks, Months, Quarters, and Years
Replacing Character Values With Na in a Data Frame
Count Number of Occurences For Each Unique Value
Warning Message: in '...': Invalid Factor Level, Na Generated
How to Test When Condition Returns Numeric(0) in R