QB Formula to categorize months

Question

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------------------------------

markshnier__you · Answer

Try thisvar date DDFirst = FirstDayOfMonth([Dispatch Date]);IF($DDFirst = FirstDayOfMonth(Today()),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;"1. Current Month",$DDFirst = FirstDayOfMonth(AdjustMonth(Today(),-1)), "2. Previous Month",$DDFirst = FirstDayOfMonth(AdjustMonth(Today(),1)),&nbsp; "3. Next Month",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "4. All Others")------------------------------Mark Shnier (YQC)Quick Base Solution ProviderYour Quick Base Coachhttp://QuickBaseCoach.commark.shnier@gmail.com------------------------------

nirajshah4 · Answer

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
------------------------------

markshnier__you · Answer

IF($DDFirst = FirstDayOfMonth(Today()),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;"1. Current Month",$DDFirst = FirstDayOfMonth(AdjustMonth(Today(),-1)), "2. Previous Month",$DDFirst = FirstDayOfMonth(AdjustMonth(Today(),1)),&nbsp; "3. Next Month",$DDFirst &gt;= FirstDayOfMonth(AdjustMonth(Today(),2)),&nbsp; "4. Two Months Onwards")I was&nbsp; not clear about this "ask" about this&nbsp;"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.&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;------------------------------Mark Shnier (YQC)Quick Base Solution ProviderYour Quick Base Coachhttp://QuickBaseCoach.commark.shnier@gmail.com------------------------------

celinehur1 · Answer

Hi Mark, I am using your formulas here to do something different.&nbsp;
The following will give me trailing twelve months correct?&nbsp; &lt;= FirstDayOfMonth(AdjustMonth(Today(),-12))Can you help me with trailing twelve month formula for the previous year?------------------------------Celine Hur------------------------------

markshnier__you · Answer

Celine, I think that it would be better if you posted a new question which outlined what you are trying to do.------------------------------Mark Shnier (YQC)Quick Base Solution ProviderYour Quick Base Coachhttp://QuickBaseCoach.commark.shnier@gmail.com------------------------------

Forum Discussion

QB Formula to categorize months

Related Content

Delete Formula Button Error

Formula Assistance

Formula Url Buttons

Formula Query with custom key

Happy Women's History Month!