60

I'm trying to subtract n months from a date as follows:

maturity <- as.Date("2012/12/31")

m <- as.POSIXlt(maturity)

m$mon <- m$mon - 6

but the resulting date is 01-Jul-2012, and not 30-Jun-2012, as I should expect. Is there any short way to get such result?

starball
  • 20,030
  • 7
  • 43
  • 238
user648905
  • 601
  • 1
  • 5
  • 3
  • 1
    Possible duplicate of [Add a month to a Date](http://stackoverflow.com/questions/14169620/add-a-month-to-a-date) – Barker Jan 05 '17 at 00:08
  • 2
    @Barker This old question of 2011 already has an excellent answer summarizing several possible approaches. So, I don't see reason to declare this as a duplicate of a Q of 2013. Perhaps, it should be the other way around? – Uwe Jan 05 '17 at 07:25
  • @UweBlock The other question is also many years old and also has many excellent answers. Also the answers on the other question deal with edge cases this one does not. My reason for marking as duplicate was this question was the one I found most easily and it didn't fix my problem, it was only after searching much harder that I found the other question that did have the answer I needed ([specifically the second one](http://stackoverflow.com/a/14182326/2943311)). Meta says the [less complete question should be the duplicate even if is newer](http://meta.stackoverflow.com/a/252017/2943311). – Barker Jan 05 '17 at 14:59
  • @Barker [G. Grothendiek's answer](http://stackoverflow.com/a/5226089/3817004) did already include `lubridate` and has been updated. – Uwe Jan 05 '17 at 15:32
  • @UweBlock ok, would you like me to mark the other question as duplicate then? How do I remove the flag from this question? – Barker Jan 05 '17 at 19:06

6 Answers6

121

1) seq.Date. Note that June has only 30 days so it cannot give June 31st thus instead it gives July 1st.

seq(as.Date("2012/12/31"), length = 2, by = "-6 months")[2]
## [1] "2012-07-01"

If we knew it was at month end we could do this:

seq(as.Date(cut(as.Date("2012/12/31"), "month")), length=2, by="-5 month")[2]-1
## "2012-06-30"

2) yearmon. Also if we knew it was month end then we could use the "yearmon" class of the zoo package like this:

library(zoo)
as.Date(as.yearmon(as.Date("2012/12/31")) -.5, frac = 1)
## [1] "2012-06-30"

This converts the date to "yearmon" subtracts 6 months (.5 of a year) and then converts it back to "Date" using frac=1 which means the end of the month (frac=0 would mean the beginning of the month). This also has the advantage over the previous solution that it is vectorized automatically, i.e. as.Date(...) could have been a vector of dates.

Note that if "Date" class is only being used as a way of representing months then we can get rid of it altogether and directly use "yearmon" since that models what we want in the first place:

as.yearmon("2012-12") - .5
## [1] "Jun 2012"

3) mondate. A third solution is the mondate package which has the advantage here that it returns the end of the month 6 months ago without having to know that we are month end:

library(mondate)
mondate("2011/12/31") - 6
## mondate: timeunits="months"
## [1] 2011/06/30

This is also vectorized.

4) lubridate. This lubridate answer has been changed in line with changes in the package:

library(lubridate)
as.Date("2012/12/31") %m-% months(6)
## [1] "2012-06-30"

lubridate is also vectorized.

5) sqldf/SQLite 

library(sqldf)
sqldf("select date('2012-12-31', '-6 months') as date")
##         date
## 1 2012-07-01

or if we knew we were at month end:

sqldf("select date('2012-12-31', '+1 day', '-6 months', '-1 day') as date")
##         date
## 1 2012-06-30
jay.sf
  • 60,139
  • 8
  • 53
  • 110
G. Grothendieck
  • 254,981
  • 17
  • 203
  • 341
  • Hi, thanks for your reply, but the problem is a little bit more tricky. I need to calculate a date that is, for example, 6 months prior another one (i.e. 30-Jun, should the input date be 31-Dec). – user648905 Mar 12 '11 at 08:41
  • I do not know if the starting date is a month end or not. What I would like to get is a date with the same day of the starting one, should this be a valid one (i.e. 15-Mar, should the input date be 15-Sep), or, otherwise, with the last day of the month (i.e. 28-Feb, should the input date be 31-Aug). In my question, I referred to an end of month date, as for other dates the formula I used worked fine. Should I have to define a function for doing this? Thanks – user648905 Mar 12 '11 at 08:59
  • See the `mondate` solution in the response. – G. Grothendieck Mar 12 '11 at 13:01
  • 3
    Strange. If I use x=2 or 4 for `as.Date("2012/12/31") - months(x)` I got the expected results. With x = 1,3,5,6 I got NA. Any idea why this happens? – giordano Mar 31 '15 at 13:40
  • 3
    @giordano its because some months don't have 31 days. Try `as.Date("2012/12/20") - months(1:12)` for example. – David Arenburg Sep 21 '15 at 10:31
10

you can use lubridate package for this

library(lubridate)
maturity <- maturity %m-% months(6)

there is no reason for changing the day field.

you can set your day field back to the last day in that month by

day(maturity) <- days_in_month(maturity)
desertnaut
  • 57,590
  • 26
  • 140
  • 166
Aneesh Cherian K
  • 396
  • 5
  • 7
4

lubridate works correctly with such calculations:

library(lubridate)
as.Date("2000-01-01") - days(1)    # 1999-12-31
as.Date("2000-03-31") - months(1)  # 2000-02-29

but sometimes fails:

as.Date("2000-02-29") - years(1)   # NA, should be 1999-02-28
J. Doe
  • 71
  • 2
1

tidyverse has added the clock package in addition to the lubridate package that has nice functionality for this:

library(clock)

# sequence of dates
date_build(2018, 1:5, 31, invalid = "previous")
[1] "2018-01-31" "2018-02-28" "2018-03-31" "2018-04-30" "2018-05-31"

When the date is sequenced, 2018-02-31 is not a valid date. The invalid argument makes explicit what to do in this case: go to the last day of the "previous" valid date.

There is also a series add functions, but in your case you would use add_months. Again it has the invalid argument that you can specify:

x <- as.Date("2022-03-31")

# The previous valid moment in time
add_months(x, -1, invalid = "previous")
[1] "2022-02-28"

# The next valid moment in time, 2022-02-31 is not a valid date
add_months(x, -1, invalid = "next")
[1] "2022-03-01"

# Overflow the days. There were 28 days in February, 2020, but we
# specified 31. So this overflows 3 days past day 28.
add_months(x, -1, invalid = "overflow")
[1] "2022-03-03"

You can also specify invalid to be NA or if you leave off this argument you could get an error.

LMc
  • 12,577
  • 3
  • 31
  • 43
0

Technically you cannot add/subtract 1 month to all dates (although you can add/subtract 30 days to all dates, but I suppose, that's not something you want). I think this is what you are looking for

> lubridate::ceiling_date(as.Date("2020-01-31"), unit = "month")
[1] "2020-02-01"
> lubridate::floor_date(as.Date("2020-01-31"), unit = "month")
[1] "2020-01-01"
ishonest
  • 433
  • 4
  • 8
0

UPDATE, I just realised that Tung-nguyen also wrote the same method and has a two line version here https://stackoverflow.com/a/44690219/19563460 Keeping this answer here so newbies can see different ways of doing it

With the R updates, you can now do this easily in base R using seq.date(). Here are some examples of implementing this that should work without additional packages

ANSWER 1: typing directly

maturity <- as.Date("2012/12/31")

seq(maturity, length.out=2, by="-3 months")[2]

# see here for more help
?seq.date

ANSWER 2: Adding in some flexibility, e.g. 'n' months

maturity <- as.Date("2012/12/31")
n  <- 3
bytime <- paste("-",n," months",sep="")

seq(maturity,length.out=2,by=bytime)[2]

ANSWER 3: Make a function

# Here's a little function that will let you add X days/months/weeks
# to any base R date.  Commented for new users

#---------------------------------------------------------
# MyFunction
# DateIn, either a date or a string that as.Date can convert into one
# TimeBack, number of units back/forward
# TimeUnit, unit of time e.g. "weeks"/"month"/"days"
# Direction can be "back" or "forward", not case sensitive
#---------------------------------------------------------

MyFunction <- function(DateIn,TimeBack,TimeUnit,Direction="back"){
   #--- Set up the by string
   if(tolower(Direction)=="back"){
      bystring <- paste("-",TimeBack," ",tolower(TimeUnit),sep="")
   }else{
      bystring <- paste(TimeBack," ",tolower(TimeUnit),sep="")
   }   

   #--- Return the new date using seq in the base package
   output <- seq(as.Date(DateIn),length.out=2,by=bystring)[2]
   return(output)
}


# EXAMPLES
  
MyFunction("2000-02-29",3,"months","forward")


Answer <-  MyFunction(DateIn="2002-01-01",TimeBack=14,
         TimeUnit="weeks",Direction="back")
print(Answer)


maturity <- as.Date("2012/12/31")
n  <- 3
MyFunction(DateIn=maturity,TimeBack=n,TimeUnit="months",Direction="back")

ANSWER 4: I quite like my little function, so I just uploaded it to my mini personal R package.

This is freely available, so now technically the answer is use the JumpDate function from the Greatrex.Functions package

Can't guarantee it'll work forever and no support available, but you're welcome to use it.

# Install/load my package
install.packages("remotes")
remotes::install_github('hgreatrex/Greatrex.Functions',force=TRUE)
library(Greatrex.Functions)

# run it
maturity <- as.Date("2012/12/31")
n  <- 3
Answer <- JumpDate(DateIn=maturity,TimeBack=n,TimeUnit="months",
         Direction="back",verbose=TRUE)
print(Answer)


JumpDate("2000-02-29",3,"months","forward")

# Help file here
?Greatrex.Functions::JumpDate

You can see how I made the function/package here: https://github.com/hgreatrex/Greatrex.Functions/blob/master/R/JumpDate.r

With nice instructions here on making your own mini compilation of functions. http://web.mit.edu/insong/www/pdf/rpackage_instructions.pdf
and here How do I insert a new function into my R package?

Hope that helps! I hope it's also useful to see the different levels of designing an answer to a coding problem, depending on how often you need it and the level of flexibility you need.

Helen Greatrex
  • 1
  • 1