10 Replies Latest reply: May 15, 2014 10:06 AM by Grant Perkins RSS

    MonthName

    Rusch Mauzy

      I am using version 10 Pro

       

      I have a field name DATE formatted as

      10.0201

      10.0202

      10.0203

       

      I want to calculate the Month Name.

       

      I am trying a formaula-based calculated field.

       

      In the formula window I enter MonthName_Eng(DATE) and when I press enter, the formuala returns

      a large red X and the message Operand Types

        • MonthName
          Data Kruncher

          Just to be sure, is that Year.MonthDay yy.mmdd Rusch?

           

          And what data type is your original date field?

            • MonthName
              Data Kruncher

              Assuming that your Date is a Character field, you can use:

               

              MonthName_Eng(

              CtoD(

              Left(Date,2)"/"

              Substr(Date,4,2)"/"

              Right(Date,2),

              "y/m/d"))[/CODE]

               

              if however your Date is Numeric, then use:

              MonthName_Eng(

              CtoD(

              Right("00"Trim(Str(Date,7,0)),2)"/"+

              Substr(Trim(Str(Date,7,4)),Len(Trim(Str(Date,7,4)))-3,2)+"/"

              +Right(Trim(Str(Date,7,4)),2),"y/m/d"))[/CODE]

                • MonthName
                  Grant Perkins

                  Rusch, out of interest what is the source system for that date format. It's not one I have seen before.

                   

                  If you come across it a lot, once you have resolved the requirement with the appropriate routine (from Kruncher's options?) I would save it somewhere readily accessible as a User Defined Function so that you can simply call it up next time you need it.

                   

                  HTH.

                   

                   

                  Grant

                    • MonthName
                      Rusch Mauzy

                      The source system is Ross, used in the food industry, thank you for the tip to create a user defined function, I will do it.

                       

                      My original date field is numeric.  and the format is yy.mmdd.

                       

                      I applied the numeric formula and it works like a charm.

                       

                      Thank you,

                       

                      Rusch

                       

                      Using the numeric formula is it possible to extract the two digit year, also.

                       

                      Such as February 09, or January 10.

                        • MonthName
                          Data Kruncher

                          MonthName_Eng(

                          CtoD(

                          Right("00"Trim(Str(NumDate,7,0)),2)"/"+

                          Substr(Trim(Str(NumDate,7,4)),Len(Trim(Str(NumDate,7,4)))-3,2)+"/"

                          Right(Trim(Str(NumDate,7,4)),2),"y/m/d"))

                          " "+

                          Right("00"+Trim(Str(NumDate,7,0)),2)[/CODE]

                            • MonthName
                              Rusch Mauzy

                              Thank You,

                               

                              Rusch

                                • MonthName
                                  Rusch Mauzy

                                  What portion of the string can I alter to shorten the Month name to 3 characters?  December = Dec, February = Feb, etc.

                                   

                                  Thank you,

                                   

                                  Rusch

                                    • MonthName
                                      Data Kruncher

                                      This:

                                       

                                      MonthName_Eng(

                                      CtoD(

                                      Right("00"Trim(Str(NumDate,7,0)),2)"/"+

                                      Substr(Trim(Str(NumDate,7,4)),Len(Trim(Str(NumDate,7,4)))-3,2)+"/"

                                      +Right(Trim(Str(NumDate,7,4)),2),"y/m/d"))[/code]converts 10.0201 into February, so if you put all of that into a Left(formula,3) then you'll get Feb.

                                      Left(MonthName_Eng(

                                      CtoD(

                                      Right("00"Trim(Str(NumDate,7,0)),2)"/"+

                                      Substr(Trim(Str(NumDate,7,4)),Len(Trim(Str(NumDate,7,4)))-3,2)+"/"

                                      +Right(Trim(Str(NumDate,7,4)),2),"y/m/d")),3)[/code]Give the flexibilty that Monarch offers, there are probably 10 other ways to do this too.

                                • MonthName
                                  Grant Perkins

                                  The source system is Ross, used in the food industry, thank you for the tip to create a user defined function, I will do it.

                                   

                                  /quote

                                   

                                  Rusch,

                                   

                                  Thanks for the update. Interesting that they use that particular format - I wonder of it is a food industry thing that allows them to calculate things like 'Use by' dates most easily?

                                   

                                   

                                  Grant