10 Replies Latest reply: May 15, 2014 10:02 AM by JoeMcG _ RSS

    Mmm-yy

    JoeMcG _

      Hello: My report has both a Run Date and a Report Month. Report Month is in MMM-YY format. I like MMM-YY for display purposes, but as a character field it sorts by alpha, which isn't goodl.

       

      Thanks to many posting by Grant and Kruncher, I know how to convert the "character" MMM-YY to a Date/time field (function CTOD).

       

      Call me greedy, but I want the Date to retain the MMM-YY format for display and still sort like a Date.

       

      Plan B is to take Run date minus a day (gets us into the report month) and then using Excel to display it the way I want it.

       

      Any suggestions?

        • Mmm-yy
          Data Kruncher

          While I haven't attempted this yet, I would think that you want two date fields for this: one that is the "real" date field, and one that's looks like a date, but is actually a Character field built to look like a date.

           

          Use the Date type field for sorting the table, and just hide it, leaving you with the Character Date for presentation purposes.

           

          What do you think? Would this resolve your situation?

            • Mmm-yy
              Data Kruncher

              As to creating the Mmm-yy style Character date, it takes a couple of steps to get there. First, use the Month() function to get a 1-12 value.

               

              Next, build a lookup calculated field (MMM) based on the Month number with the 1 to 12 values with Jan through Dec respectively.

               

              Finally, you can build the formatted date with:

              [SIZE=2]MMM"-"Right(Str(Year(Period)),2)[/SIZE][/code]

               

              where Period is your Date field.

               

              How's that?

                • Mmm-yy
                  JoeMcG _

                  As per your first suggestion, my table is already sorted by report date, which remains hidden. I didn't realize how close I was to solving for my summary.

                   

                  Can I "hide" report date in a summary? I could hide it in Excel I suppose.

                   

                  Thank you for both solutions!

                  • Mmm-yy
                    JoeMcG _

                    Isn't building a character style date redundant? It would still sort as character, yes?

                    What I like about this tip is the Lookup Calc Field which I hadn't used before. Like your signature lines says, "always searching..."

                      • Mmm-yy
                        Nick Osdale-Popa

                        If you want to keep your formula all intact, without using a lookup formula, you can use the following to get your MMM-YYYY formatted date:

                        SUBSTR("JANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC",(Month()*3)-2,3)'-'Trim(Str(Year(MyDate)))[/code]

                         

                        EDIT:

                        I think I understand your problem now. Yes, creating a MMM-YYYY field is redundant.

                        You'll need to follow DK's first suggestion: Use a formula to convert the Report Month field into a true Date Field and sort by this new field. Hide if necessary.

                        For the summary, include this newly created field, but in the properties click on the display tab and select Hide The Key Column (ver 9)

                         

                        Hope that helps!

                  • Mmm-yy
                    JoeMcG _

                    thanks DK, Nick, and Bruce