# Discussions

View Only

## QB Formula to categorize months

• #### 1.  QB Formula to categorize months

Posted 20 days ago
I have the following summary report:

What I would really like to do is somehow get the row grouping (Dispatch Date) to be based off of the following groupings: Current Month, Previous Month, Next Month, All Others.

What would be the most efficient way to get this done? Currently, I am looking at creating a few extra fields in the table and calculating the current month and the dispatch date relevant to the current month for all the records in the table.

------------------------------
Niraj Shah
------------------------------

• #### 2.  RE: QB Formula to categorize months

Posted 20 days ago
Try this
var date DDFirst = FirstDayOfMonth([Dispatch Date]);

IF(
\$DDFirst = FirstDayOfMonth(Today()),                               "1. Current Month",
\$DDFirst = FirstDayOfMonth(AdjustMonth(Today(),-1)), "2. Previous Month",
\$DDFirst = FirstDayOfMonth(AdjustMonth(Today(),1)),  "3. Next Month",
"4. All Others")

------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
http://QuickBaseCoach.com
mark.shnier@gmail.com
------------------------------

• #### 3.  RE: QB Formula to categorize months

Posted 18 days ago
Thanks, Mark. This worked well and I was able to include it in the report formula area.

I am now facing a scenario where my "All Others" section includes dates 2 months onwards but also more 2 months previously. How would I update the formula to change "4. All Others" to "4. 2 Months Onwards" that also excludes anything more previous than the last month?

Thank you.

------------------------------
Niraj Shah
------------------------------

• #### 4.  RE: QB Formula to categorize months

Posted 18 days ago
IF(
\$DDFirst = FirstDayOfMonth(Today()),                               "1. Current Month",
\$DDFirst = FirstDayOfMonth(AdjustMonth(Today(),-1)), "2. Previous Month",
\$DDFirst = FirstDayOfMonth(AdjustMonth(Today(),1)),  "3. Next Month",
\$DDFirst >= FirstDayOfMonth(AdjustMonth(Today(),2)),  "4. Two Months Onwards")

"that also excludes anything more previous than the last month?"

The formula above will calculate to null if it's not a 1,2,3 or 4.

------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider