CODEWITHSUNDEEP
r
Wolfgang Wu
Wolfgang Wu

Reputation: 864

R date to Excel based number

I know that I can get a date from an Excel based number (days since 1899-12-30) in the following way:

as.Date(41000, origin = "1899-12-30")

which will give me "2012-04-01". I want however the opposite. As a user I would like to input a date as a string and get the number of days since "1899-12-30".

Something along the lines

as.integer(as.Date('2014-03-01', origin="1899-12-30"))

which I hoped would result in 41000 and not in the R based days since 1970-01-01 which is 15431.

Maybe this is silly as I realize that I can add the days manually by writing something like:

as.integer(as.Date('2012-04-01')) + 25569

I just wondered if there is a function which does this?

Upvotes: 2

Views: 800

Answers (2)

Barranka
Barranka

Reputation: 21067

Do it by hand, simpler and safer:

d0 <- as.Date('1899-12-30')
d1 <- as.Date('2014-10-28')

as.integer(d1 - d0)
##[1] 41940 # This is interpreted by Excel as '2014-10-28'

Of course, you can write a function to convert a R date to an Excel one:

convert_to_excel_date <- function(d) {
  # Converts a R date value to an Excel date value
  #
  # Parameters:
  #   d: a R date object
  d0 <- as.Date('1899-12-30')
  return(as.integer(d - d0))
}
# Example:
# convert_to_excel_date(as.Date('2018-10-28'))

Upvotes: 2

Tyler Rinker
Tyler Rinker

Reputation: 110062

I think you want difftime as in:

difftime(as.Date('2012-04-01'), as.Date("1899-12-30"))

## Time difference of 41000 days

Upvotes: 4

Related Questions