Function to find starting month of a quarter (custom) in R

Question

I have written a function to find the starting month of a quarter. Before anyone says it has been answered already, I am not dealing standard quarters(Jan to Mar, Apr to Jun etc). Quarter could be custom, say something like Mar to May, Jun to Aug etc. I have also searched for some packages like timeDate, zoo etc, it looks like they also don't deal with custom quarters.

The function I have written is not working for some cases.

My function

opening_month = function(s, c) {
  return(
    ifelse(s==c, s, 
           ifelse(s<c, (s+3*floor((c-s)/3)), (s+3*ceiling((c-s)/3)))
    )
  )
}

# S - Lead month of the quarter
# C - Month of Date in question

Every data point has these two fields. S specifies when does a quarter start and C is the date in question for which we need find the starting month of whatever quarter it belongs to.

data looks like (only first 3 columns)

+----+---------------------+----------------+---------+----------------+
| No | Creation Date ( C ) | Lead month (S) | Quarter | Starting month |
+----+---------------------+----------------+---------+----------------+
|  1 | 04 March 2015       |              1 | 1 to 3  |              1 |
|  2 | 04 March 2015       |              2 | 2 to 4  |              2 |
|  3 | 01 January 2015     |              6 | 12 to 2 |             12 |
|  4 | 20 November 2015    |              5 | 8 to 10 |              8 |
+----+---------------------+----------------+---------+----------------+

ex: No 3. Lead month = 3. so The cycle is Jun to Aug, Sep to Nov, Dec to Feb and Mar to May. the creation date 1st Jan fall between Dec to Feb. so Dec is the starting month which is what is the final result.

The function takes lead month (S) and extracted month no from Creation date (C) to find the starting month of the quarter to which it belongs to.

Ex: C - 6 (21-Jun-2015), S - 1

For this data point Quarter starts in Jan. so it's the standard cycle. Jan to Mar, Apr to Jun etc. 21-Jun-2015 belongs to 2nd quarter and starting month in 2nd quarter is April or month no 4.

opening_month(1,6)
[1] 4

My function works well in this case.

opening_month(2,6)
[1] 5

My function also works well in this case.

Quarter starting in Feb, the cycle is Feb to April, May to Jul etc. Jun falls in 2nd quarter and the starting month is May or month no 5.

opening_month(6,3)
[1] 3

This is also correct. In this case quarter starts from Jun. Jun to Aug, Sep to Nov, Dec to Feb, Mar to May. It's 4th quarter and the starting month is Mar itself.

There are some case where my function does work.

opening_month(6,1)
[1] 3

In this case quarter starts from Jun. Jun to Aug, Sep to Nov, Dec to Feb. It's 3rd quarter and the starting month should December. But function gives Mar or month no 3 as result.

I know the limitation here. My function doesn't capture the circular relationship of months. It doesn't know Dec preceeds Jan and the cycle is circular Jan to Dec to Jan.

Can someone help me overcome this problem ?

Answer 1

With this alternative approach one can tailor made the periods as needed, which may come in handy to someone out there looking at a similar issue:

period.beginning <- function(input.month,starting.period.months.vector){

good.months <- starting.period.months.vector*c(1:12)
good.months <- good.months[good.months!=0]

months.ref <- c(1:12)
months.ref[1] <- ifelse(starting.period.months.vector[1],1,tail(good.months,1))

for(x in 2:12){
  if(!starting.period.months.vector[x]){months.ref[x] <- months.ref[x-1]}
}

return(months.ref[input.month])

}

Examples:

period.beginning(2,c(F,F,T, F,F,T, F,F,T, F,F,T))   # quarters starting in March, July, Sept, Dec
[1] 12

period.beginning(2,c(F,F,F, F,T,F, F,F,F, F,T,F))   # two weird semesters, starting in May and Nov
[1] 11

Answer 2

I have written some code which is working for now, but I am sure a much better (efficient) code can be written.

opening_month = function(s, c) {
  return(
    ifelse(s==c, s, 
           ifelse(s<c, s+3*floor((c-s)/3), 
              ifelse(
                (s+3*floor((c+12-s)/3))>12, 
                  s+3*floor((c+12-s)/3)-12, 
                    s+3*floor((c+12-s)/3)
                    )
                )
          )
    )
}

Below are results for some sample obervations

> opening_month(1,2)
[1] 1
> opening_month(1,5)
[1] 4
> opening_month(6,5)
[1] 3
> opening_month(6,1)
[1] 12
> opening_month(10,2)
[1] 1