How to Convert Excel Date Format to Proper Date in R

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 NAs, 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