Home > FAQ > Data Manipulation > Date Rounding in Expressions > Beginning of the next ... examples > Beginning of the next quarter (calendar_year)

Beginning of the next quarter (calendar_year)

This is the recommended expression for rounding dates to the beginning of the next calendar year quarter:

BOY1_DT:= 3 #MONTHROUND (A_DT + 1) &
; add one day to every date to take advantage of coincident or following rounding

A_DT may be either a single date (e.g., 7/16/2015, DOH, #DODEC) or an array of dates (e.g., #DATE).  

Why are we adding 1 day to A_DT before we round the date(s)? Because 3 #MONTHROUND rounds to the coincident or following beginning of calendar year quarter. By adding 1 day, 1/1/2020 becomes 1/2/2020, and 3 #MONTHROUND 1/2/2020 returns 4/1/2020, the beginning of the next calendar year quarter. If A_DT is 3/31/2020, then adding 1 day gives us 4/1/2020, which is the beginning of the next calendar year quarter, so 3 #MONTHROUND 4/1/2020 returns 4/1/2020 (i.e., coincident or following), the beginning of the next calendar year quarter for 3/31/2020.

 

If you want to be consistent with the beginning of the next plan year quarter methodology, then we recommend this expression:

BOY1_DT:= #NEXTBEGMTH (5 #LSTBUSDAY A_DT) &
; find the last business day of the current calendar year quarter, then round that last business day
; to the beginning of the next month

We start with #LSTBUSDAY because it's the easiest way to work with plan years but can be generalized to calendar years. We then use #NEXTBEGMTH to guarantee that we are at the beginning of the next calendar year quarter. 5 #LSTBUSDAY A_DT finds the last business day of the current calendar year quarter, which is one of the last 3 days of the current calendar year quarter, depending on whether the last day of the current calendar year quarter is a weekday, Sunday or Saturday. Then #NEXTBEGMTH converts it to the first day of the next month (i.e., the first day of the next calendar year quarter).