Create Calculated Month Field to Sort and Group

[Tom Whiteside]

You can change your Windows - Control Panel - Regional Settings - Date - Long Date Style setting from it’s relatively useless day-of-the-week default setting to MMM-yyyy. This will allow you to use a calculated Monarch field for "Month" to full advantage, namely, as "Jul-2002," or, "Aug-2002," and have it sort as would a real date, in proper calendar order.

Where I wish a real month field of "Jul-2002" (not a text field of "JUL-2002") and the only dates in my report are text fields of "07-JUL-02" or "14-JUL-02," using the calculated field of:

Month=Ctod("01-"+Substr([Txt_Date],4,6))

works by stripping off the "07-" or the "14-" and replacing them with "01-" and then using CTOD to transform the new text date field of “01-JUL-02” to a true date of “01-Jul-02” and display it as “Jul-2002.”. Again, you must have a Windows/Monarch date setting option of "MMM-yyyy" to make the "01/07/02" display as "Jul-2002."

So, in brief:

1) Changing the Windows Long Date Setting to MMM-yyyy will let you sort and group your text report data by calculated month fields of "Apr-2002," "May-2002," "Jun-2002," and so on, using calculated dates of 4/1/02, 5/1/02, or 6/1/02.

2) Simply using the Windows Long Date Setting of MMM-yyyy to format regular dates (4/21/02, 5/17/02, 6/8/02) will give apparent month fields of "Apr-2002," "May-2002," "Jun-2002," and so on, but they will still sort as if they were individual dates. Seventeen different dates in April will produce seventeen identically formatted fields of "Apr-2002," but they will sort and group as seventeen different entries.

Be sure to set your Monarch Field Properties to Format: Date – long, to let Monarch make use of the changed Windows Long Date Style setting.

[Mike Urbonas]

Well done Tom, this tip also comes in very handy for financial reports (GL, AP, AR related reports, etc.) which may contain specific transaction dates, but you want to combine the transactions together to view them on a monthly basis. Thanks again Tom.

Mike

[Tom Whiteside]

Just a word of caution - - one situation where you will have to switch your Long Date Style setting back to its old default of "dddd,MMMM dd,yyyy" is when using Windows Explorer to copy files.

If you are ever in danger of overwriting another file by copying an earlier version with the same name, Windows Explorer will display a pop-up screen that asks:

This folder already contains a file called 'abc.xyz'. Would you like to replace the existing file, nKB, modified on Monday, July 29, 2002, 10:30:26 AM with this one: mKB, modified on Friday, July 26, 2002, 6:17:26 PM?

However, when you have made the Monarch-enhancing change in your Long Date Style setting to "MMM-yyyy" (described in my earlier post, above), Windows Explorer will now ask:

Would you like to replace the existing file, nKB, modified on Jul-2002, 10:30:26 AM with this one: mKB, modified on Jul-2002, 6:17:26 PM? :confused:

In this one case - - and there may be others of which I am not yet aware - - you will simply have to switch the Long Date Style setting from its Monarch-friendly version of "MMM-yyyy" back to the default setting of "dddd,MMMM dd,yyyy" just for the duration of your Windows Explorer work, and then switch back to "MMM-yyyy" when you are finished. It's a small price to pay for the benefit gained in using Monarch!

Best wishes,

Tom Whiteside

[Tom Whiteside]

Several questions were sent to me directly that asked about specific date examples and how to get to the calculated month field described in the earlier posts. While there is no way to cover all of the situations, one of the questions hit a very good point, namely, what to do when your original report starts out with “real” dates, e.g., 07/14/02, instead of just a text date of “Jul-14-02.” All of my earlier advice had assumed people would start out with original text dates, as do my own example Oracle reports.

If your report starts out with a “real” date, e.g., 07/14/02, you simply use a two-step version of the formula listed. First, if your 07/14/02 date field is called, e.g., Orig_Date, then use the date-to-character function to turn it into a text date:

Txt_Date=Dtoc([Orig_Date])

Because of the mm/dd/yy format, use a modification of the dd/mmm/yy formula used in my original tip:

Month=Ctod(Substr([Txt_Date],1,3)+”01”+Substr([Txt_Date],6,3))

This should give you the “real” date of 07/01/02 that can be translated to Jul-2002 by the change in Windows Regional Date Setting. The messy substring formula takes any text date of "mm/dd/yy" and changes it to a text date of "mm/01/yy," then Ctod changes it to a “real” date of mm/01/yy. Again, all records within a specific month will have a [Month] field of mm/01/yy that will show up as mmm-yyyy after the regional settings change.

As always, feel free to write me if my examples are not clear. :confused:

Tom Whiteside
whitesidetom@yahoo.com