5 Replies Latest reply: May 15, 2014 10:05 AM by Hirono _ RSS

    Date Format Recognition on Monarch

    Hirono _

      Hello. I would appreciate any suggestions you could provide me; my Question is---

       

      How would you suggest me the best way to convert a data as date format if the original format is found as below on the original text file?

       

      20 Sep 2009

       

      This format can't be recognized as date format in Monarch, but I try to

      extend to show day such as "Mon, Tue, Wed" in my model if the data is recognized as date.   Thank you. Hirono

        • Date Format Recognition on Monarch
          Grant Perkins

          Hello Hirono and welcome to the forum.

           

          You date format is a standard UK date format and should be recognised by Monarch if you change the Options>Input Options setting for the date format to D/M/Y. I will guess that you have the default US settings in place.

           

          The Date Input Format is saved in the Model you create so that next time you work with the same report and model combination the dates will be recognised even if you have changed your default Date Input setting (meaning your current date input setting) to a different option so that you can work with other reports.

           

          Monarch will interpret "Sep" as "September" if you set the FIELD parameter to the LONG DATE option. If you use the SHORT DATE option it will display as 20/09/2009.

           

          If you want to have the display in some other format things get a little more complicated but there are many ways you can do that too. Once Monarch understands the date to be a date you should be able to use the "Weekdayname_eng()" function (From the User Defined Functions model) to give you the day of the week in English directly or the Weekday() function to get a day number from which you could link to a lookup to whatever text you want to use. (You could then save that as your own User Defined function for future use.)

           

          I hope this helps.

           

          If it does not solve you problem let us know and we can look for a different solution.

           

          Grant

            • Date Format Recognition on Monarch
              Hirono _

              Mr. Grant, thank you.  I am currently away from office/pc where Monarch is installed.  So, I say thank you for now, and I ask you in advance if I would need your futher assistance. Thank you for your advise.

                • Date Format Recognition on Monarch
                  Hirono _

                  Mr. Grant, thank you for your advice  

                   

                  I changed input settings.  From that point, I could set another column based on calculation following your additional advice.  Thank you so much.  I do appreciate your kindly support.  

                   

                  So, I just learned the good of joining of Monarch Forum as well.  Thank you.

                    • Date Format Recognition on Monarch
                      Grant Perkins

                      I am glad you have solved you problem Hirono.

                       

                      By the way it occurred to me that if you want to abbreviate the day name provided by the "Weekdayname_Eng()" function you could:

                       

                      modify the formula in the function to present the text you want

                      /LIST

                      modify and then save as a NEW function and so also keep the original function with no change.

                      /LIST

                      include the LEFT() function in your formula to return only the specified number of characters from the string. Left(STRING_from_Expression,3)[/code] would give Mon, Tue, Wed instead of Monday, Tuesday, Wednesday .....

                      /LIST

                      If you wanted some flexibility in that number of characters to use you could make the number a variable that could be set by the user as a runtime option field.

                       

                      Left(STRING=from-Expression,[User Runtime Value field])[/code]

                       

                      would work for you there.

                       

                       

                       

                      This is probably making things more complicated than you need at this time BUT as an example of Monarch's flexibility you may find benefit from taking 2 or 3 minutes to experiment with it. Even if it is of no use for this question it will probably be useful for you at some point in the future.

                       

                      Have fun.

                       

                       

                       

                      Grant