Month,Day,Year=Month

[Leather Knight]

In a report, is there a way to have a field that is set to show 12/31/05 turn up as "December" so in reports I can sort by just month? Thanks!

[Zero Tolerence]

MonthName( "12/31/2005" )

[LaRetta]

There is a problem with this of course ... the months created will sort alpha by month which will NOT be in proper month-number sequence. Secondly, hard-coding the date is simply a bad idea. And it also doesn't take into account the year in the sort. You will never get a correct sort from MonthName(). Instead you might try creating a calculation (result is date) called cMonth with:

yourDateField - Day ( yourDateField ) + 1

or even

Date ( Month ( yourDateField ) ; 1 ; Year ( yourDateField ) )

Either of these formula provides a generic date field which turns all your dates into the first of the month. If you use this new calc for all sorts and reports, you will always get a perfect sort regardless of the found set. So even a found set which overlaps years will group and sort properly by month.

In your report, simply place this new calc in your leading part, right-click the field and change its date format to display only the month name (and/or year if you wish) - display format can be anything. It provides a calc that you will use for many things because of its flexibility simply by changing its field-level display format. You will also find yourself using it for joins. Learned this tid-bid from Comment.

But you wrote 12/31/05. I hope you are using a REAL date here and you actually meant 12/31/2005. If you are using text, I highly suggest changing it to a real date instead so you can take advantage of FM's date capabilities.

LaRetta

[Ender]

You can sort correctly my that calculated MonthName if you define a value list with the month names in proper order, then use that for a custom sort order. However, it may be easier to simply define the calc to use Month(date), as this returns the numeric month number. Then sort by that.

[LaRetta]

"Month(date), as this returns the numeric month number"

Correct. However, don't plan on overlapping years or it will improperly group. Otherwise you'll also have to create a calc for the year and then another calc to combine them. Your suggestion means adding a calc (date) AND a value list; when one new date field will do it all and fill many other uses as well. :wink2:

[Zero Tolerence]

Honestly if you want to sort quickly by date why not just create a calc field GetAsNumber(Date) and sort by it?

And of course I didn't mean to hard code the date in there, I was just using his example :

[Ender]

Hi LaRetta,

Good point about the years. If the report could span multiple years, then the years should be accounted for.

Personally, I summarize by School Year or Fiscal Year field first, then by Month. But one could just as well use a calculated Year field. In any case, I'd still use two separate fields just to give the summary report another category to break by. So the summary report parts might look like:

Header

---------------------------

Sub-Summary by Year (Leading)

---------------------------

Sub-Sumary by Month (Leading)

---------------------------

Sub-Summary by Customer

---------------------------

Sub-Summary by Month (Trailing)

---------------------------

Sub-Summary by Year (Trailing)

---------------------------

Footer

[LaRetta]

" Honestly if you want to sort quickly by date why not just create a calc field GetAsNumber(Date) and sort by it? "

Zero, if this were the case, you could simply sort by the date field itself! This doesn't provide a field in which to GROUP or display the month name).

Mike, I've gone round-about on the sort/display/group issues with dates. I've gone the route of then wanting to concatenate year/month for sorting. You will end up with a text calculation because you must pre-fill the month number with leading zero. Overall, you will be creating many more calcs to handle it successfully. I still find that using a REAL date provides many more options. Since it GROUPS the records properly but remains a date, it becomes quite powerful and flexible.

UPDATE: In addition, if year is not required but it overlaps years, the calc would just be formatted to include the year at the display level and the report would look like:

October, 2005

November, 2005

December, 2005

January, 2006 ... etc

It provides a month report but also takes year into account. Same with a year calc (converting each date to 1/1/thatYear). By leaving them as dates (but each date becomes generic within that period) then the date display can be changed as needed AND those Month and Year calcs can be used for joining based upon 'greater and less than.' Sorting solves itself. And if you have a month (number), you can't change it's display to month name (field format number option doesn't do months). Only date fields have this flexibility and power.

So create a calc Month(yourDateField), then have to create a VL to display month name -- or, simply group the dates as I've indicated (one for month, one for year) and solve all of your month/year, sort/group display issues in one whack AND provide calcs which will be used for many other things as well.

LaRetta

Edited January 5, 2006 by Guest
Added Update

[Ender]

You will end up with a text calculation because you must pre-fill the month number with leading zero. Overall, you will be creating many more calcs to handle it successfully.

I haven't had to do either of these. One calc for the Month and one for the Year seem to work well for me.

Anyway, I'm glad you shared your solution, as it's not something I would have thought of. :wink2:

[LaRetta]

Note that I DIDN'T come up with the idea ... I just stole it.

I agree. One calc for the month and one for the year. But if the calc result is DATE, incredible things begin to happen. It takes just as little resources to make your month and year calcs DATE as it does to make them month number or year alone; and they become valuable flexible tools for sorting, relationships and all displays are flexible and handled strictly at the field-format level ...

How many times does it come up ... questions about converting month/year, year/month, month name etc. If the field IS a date, all of these convolutions and conversions and additional calculations become unnecessary - you simply change their format display as:

September, 2005

09-2005

2005 September

... options are endless; in fact, all options available to dates (even using special separators or pre-filling with leading zeros) are at your service ...

[Leather Knight]

WOW! Ok...I have reports that span over the years and will need the reports to be sorted by Month and year. I didn't think of the multiple years either.

I made a field called "Quarter" and had it have a drop down menu with:

1st-2005

2nd-2005

...and so on. For my quarterly reports. But monthly reports...well thanks to all of you, I got it. Thanks.

Edited January 8, 2006 by Guest