Assigning Dates to Fiscal Year

Question

I'm trying to come up with some code that will look at a date and then assign it to a fiscal year. I'm totally stuck.

I have a variable that contains dates in POSIXct format:

df$Date
#2015-05-01 CST
#2015-04-30 CST
#2014-09-01 CST

What I need to be able to do is take those dates and return a fiscal year, which runs from May 1 - April 30. For example, Fiscal Year 2016 runs 2015-05-01 through 2016-04-30. Results would look something like this:

df$Date                df$FiscalYear
#2015-05-01 CST        #FY2016
#2015-04-30 CST        #FY2015
#2014-09-01 CST        #FY2015

Is there any easy way to do this?

Answer 1

Here are some alternatives. They all return numeric years but if you really need a string starting with FY then use paste0("FY", result) where result is any of the results below. They all support vector input, i.e. the input dates can be a vector.

1) zoo::as.yearmon The zoo package has a "yearmon" class which represents year/months as year + fraction where fraction = 0 for jan, 1/12 for feb, 2/12 for march and so on.

Using that this one-liner will do it. It subtracts 4/12 (since April is end of year) and adds 1 (i.e. add one year). Then to get the year take the integer part:

library(zoo)

as.integer(as.yearmon(dates) - 4/12 + 1)
## [1] 2016 2015 2015

2) POSIXlt Here is a solution that does not use any packages. Convert the dates to POSIXlt class. It's mo component represents Jan as 0, Feb as 1, etc. so if we are May or later (mo is 4 or more) then the fiscal year is the following calendar year otherwise it is the current calendar year. The year component of POSIXlt objects is the number of years since 1900 so add the year to 1900 plus 1 if we are at May or later:

lt <- as.POSIXlt(dates)
lt$year + (lt$mo >= 4) + 1900
## [1] 2016 2015 2015

3) format Add the year to 1 if the month is greater than or equal to 5 (or to zero if not). This also uses no packages:

as.numeric(format(dates, "%Y")) + (format(dates, "%m") >= "05")
## [1] 2016 2015 2015

4) substr. We can extract the year using substr, convert to numeric and add 1 if the extracted month (also extracted usingsubstr) is "05" or greater.; Again no packages are used.

as.numeric(substr(dates, 1, 4)) + (substr(dates, 6, 7) >= "05")
## [1] 2016 2015 2015

5) read.table This also uses no packages.

with(read.table(text = format(dates), sep = "-"), V1 + (V2 >= 5))
## [1] 2016 2015 2015

Note: We used this as the input dates:

dates <- as.Date(c("2015-05-01", "2015-04-30", "2014-09-01"))

Answer 2

Adding to G. Grothendieck's nice response. With lubridate:

year(dates) + (month(dates) >= 5)

Answer 3

You can use seq with POSIXct objects to generate a list of the "cutpoints" or 1st day of the fiscal year for the years spanning your data, then use findInterval to compute which of the intervals a particular date falls into:

> dates <- as.POSIXct( c('2015-05-01','2015-04-30','2014-09-01'))
> fy.tmp <- seq( as.POSIXct('2000-05-01'), length=25, by='year')
> fiscalYear <- (2001:2025)[ findInterval(dates,fy.tmp) ]
> fiscalYear
[1] 2016 2015 2015

You could also use the cut function instead of findInterval if you want a factor as the result.