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?
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"))
Adding to G. Grothendieck's nice response. With lubridate:
year(dates) + (month(dates) >= 5)
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.
Try modifying this:
Federal.FY <- function(x,firstMonth=10, # I've altered this line to follow the federal fiscal year, October
fy.prefix='FY',
quarter.prefix='Q',
sep='-',
level.range=c(min(x), max(x)) ) {if(level.range[1] > min(x) | level.range[2] < max(x)) {
warning(paste0('The range of x is greater than level.range. Values ',
'outside level.range will be returned as NA.'))}
quarterString <- function(d) {
year <- as.integer(format(d, format='%Y'))
month <- as.integer(format(d, format='%m'))
y <- ifelse(firstMonth > 1 & month >= firstMonth, year+1, year)
q <- cut( (month - firstMonth) %% 12, breaks=c(-Inf,2,5,8,Inf),
labels=paste0(quarter.prefix, 1:4))
return(paste0(fy.prefix, y, sep, q))}
vals <- quarterString(x)
levels <- unique(quarterString(seq(
as.Date(format(level.range[1], '%Y-%m-01')),
as.Date(format(level.range[2], '%Y-%m-28')), by='month')))
return(factor(vals, levels=levels, ordered=TRUE))}
d <- as.Date("2016-10-02")
Federal.FY(d)
##[1] FY2017-Q1
##Levels: FY2017-Q1
