Formula to group into 2-month periods.

Saturday 14 April 2018 @ 11:06 pm

A customer asked me to group the records in a report into 2-month periods. It sounded simple but there were questions to clarify, like:

Should the 2-month period start on the even months or the odd months?
Do you label the period based on the beginning of the period or the end?

This customer decided to have six 2-month periods in each year, with the label showing the beginning date of each period. The following formula will do that if you substitute your date field in the first line:

Local DateVar x := Date ({Your.DateField});
x:= x - Day(x) +1;
If Remainder (Month(x) ,2) = 0// 0 starts periods with odd months, 1 starts with even months
then Date(DateAdd('m',-1,x)) // Outputs date at beginning of period
else x;

As it is written, this formula will create 2-month periods for Jan-Feb, Mar-April, May-June, etc. Changing the zero at the end of line four to a one will cause each period to start with an even month instead of an odd month, so the periods will be Feb-Mar, Apr-May … Dec-Jan. I expect most people will want to start with odd months so that you don’t have periods that are part of two different years.

In the example above the date returned by the formula is the first day of the period. If you prefer to return the last day of the period you can add the following additional line at the bottom:

Date(DateAdd('m',2,x)) -1 // Changes output to date at end of period

(For examples of my most popular formulas, please visit the FORMULAS page on my website.)

Comments are closed.

Recrystallize Pro

Crystal Reports Server