5 Replies Latest reply: May 15, 2014 10:07 AM by Eamon Donoghue RSS

    Convert Date

    gico1972 _

      Need some help in creating a calculated field converting a date field dd/mm/yyyy to a MMM-yy field so that i can sort data by month and year (Eg Nov-10 / Dec-10 / Jan-11 etc)

       

      Any help will be much appreciated.

       

      Thanks

        • Convert Date
          Olly Bond

          Hello Gico,

           

          MonthName_Eng(InputDateField) should give you strings like "January". Left(MonthName_Eng(InputDateField);3) would give you "Jan". For the last two digits of the year as a string, Right(str(Year(InputDateField));2) should give you what you need.

           

          I'm assuming you've got v9 or above... Bear in mind that sorting on this character field would be alphabetical (April, August, December February etc). To get sorting to work, try creating a field like "2010.01" for January 2010, using this for sorting, but hiding it and showing the "Jan-10" equivalent.

           

          This can be fiddly and might need two passes, depending on what output you're trying to achieve. Are you showing the date going across in a summary?

           

          Best wishes,

           

          Olly

          • Convert Date
            Eamon Donoghue

            Quote:

            Need some help in creating a calculated field converting a date field dd/mm/yyyy to a ddd-yy field so that i can sort data by month and year (Eg Nov-10 / Dec-10 / Jan-11 etc)

            /I

             

            Hi,

            I just need to clarify what you need as your end result.  Sorry if I sound pedantic or even 'retentive' here, but I just need to get the problem clear in my head.

             

            Imho, ddd-yy would equate to a Day Number followed by a two-digit year eg.  032-10 would equate to 01/02/2010 in dd/mm/yyyy format. 

             

            The reason I bring this up is that sorting dates in the format listed above (e.g. Jan-11, Apr-23 etc.) will sort the month names alphabetically (Apr-23 will appear before Jan-11 and may not give the result you require).  There are numerous ways of converting and sorting dates and I'm sure I (or many others on the forum) can help you solve this problem once we're clear on your exact requirements.

             

            Hth.

             

             

            Best regards,

            Eamon.

              • Convert Date
                gico1972 _

                Actually i wanted to return MMM-YYYY and not DDD-YYYY as originally posted.

                  • Convert Date
                    Eamon Donoghue

                    OK, without seeing an actual sample, I'm assuming you want to convert the date from 31/03/2010 to Mar-10 and sort the date column based on that result.  Of course, sorting by the original date column first will give the desired order.  Then it's a simple matter of changing the date format to Mar-10 etc.  I notice from your profile that you're using Monarch V8 so if that is the case, I think your options are a little limited. 

                    However, it is still possible with a little inventiveness!

                     

                    One way around the problem would be to extract the Month Number from the date column using the Month function.  So, if your date column was called ShipDate, you would use  a Calculated Field (say MonthNum) with the expression: Month(ShipDate).  This will return a number - 31/03/2010 will return 3.

                     

                    Next, you add another Calculated Field (say MonthName) with the expression:  Substr("JanFebMarAprMayJunJulAugSepOctNovDec",MonthNum,3) to extract the relevant group of 3 letters from the string.

                     

                    You can actually copy the text from this post straight into the Calculated Field expression builder and try it out.

                     

                    Hope this helps.

                     

                    Best regards,

                    Eamon

                      • Convert Date
                        Eamon Donoghue

                        Sorry, I forgot to add that you can yse Year(ShipDate) to extract the 2010 from the date.  You can then convert these numbers to strings and concatenate them into the required form.

                        But please remember to do the sorting on the original date column as this will return the correct sequence.  The rest is simply a cosmetic exercise.

                         

                        All the best,

                        Eamon.