Group by formula 1

[alvintse]

Hi,
Anyone has idea how to create a formula for grouping of years and months?
The data will return the date range we do not know. For example, the date range will be from 2002 to 2004 and not every month has data.

What I need is create a formula that will group the data by month and year. The layout will be something like that:

2/2004 (has 2 records for that month)
record 1 detail go here
record 2 detail go here
4/2004 (has data for that month)
...
...
....
6/2006 (has data for that month)
...
...

Thanks

 

[DallasAggie]

Create two Formulas:

First:
ToText(Year({DateTimeFieldHere}),0,"") & ToText(Month({DateTimeFieldHere}),0)

Second:
ToText(Month({DateTimeFieldHere}),0) & "/" & ToText(Year({DateTimeFieldHere}),0,"")

Group by the First, and place the Second in the Group section instead.

Threre might be an easier way, but this seemed to work okay for me.

 

[bdreed35]

If you have a date field, you can just group by that date field.
There is an additional option when you group by a date field called Period. Select Monthly from the drop down list.
You only need one group and it will sort everything by year and month for you.

~Brian

 

[DallasAggie]

Very cool bdreed35!

I haven't had much need for that particular function, so I didn't even know it was there. My first instinct is write a formula ... now I know! I'll give you a star for that one.

Cheers!