I have a list of dates for company fiscal years. I would like to convert all dates that lie between 1st Jan - 31st May into a new variable where it says that it belongs to the prior year. I also have dates that range between 1st June - 31st Dec and I want those years to stay the same. Example of what we want:
date year
2010-05-31 2009
2015-03-31 2014
2007-04-30 2006
2011-08-31 2011
2002-11-30 2002
Your help is much appreciated! Thank you!
You can do in base R:
> df <- data.frame(date = as.Date(c("2010-05-31", "2015-03-31", "2007-04-30", "2011-08-31", "2002-11-30")))
> df$year <- as.numeric(format(df$date, "%Y")) - (as.numeric(format(df$date, "%m")) < 6)
> df
date year
1 2010-05-31 2009
2 2015-03-31 2014
3 2007-04-30 2006
4 2011-08-31 2011
5 2002-11-30 2002
Final year is the year minus 1 if month is before June.
Using dplyr and lubridate :
library(dplyr)
library(lubridate)
df %>% mutate(year = year(date) - as.integer(month(date) <= 5))
# date year
#1 2010-05-31 2009
#2 2015-03-31 2014
#3 2007-04-30 2006
#4 2011-08-31 2011
#5 2002-11-30 2002