3 Replies Latest reply: May 15, 2014 9:56 AM by Grant Perkins RSS

    Determining Last Day of the month

    Balty G

      Have a field that contains a date and need to create a calculated field that will return the last day of the month based on the original date.

       

      Can do this in excel using :-

       

      =Date(Year(A1),Month(A1)+1,0) assuming A1 = is cell original date is stored in.

       

      This formula does not seem to translate in Monarch.

      Any ideas

      Thanks

        • Determining Last Day of the month
          Grant Perkins

          Hi Balty and welcome to the forum.

           

          Version 9 has a DataAdjust function that I think would work fine for you. It also comes with a predefined 'User Defined Function' with a long formula to do the same job and deal with things like leap years and so on. That formula would work (afaik) in V7 as well (I assume you are still using V7 based on your profile.)

           

          I have a simplified formula which will also work though at this point I have not included anything to deal with leap years. However if you wish to have a look at it as a starting point here it is.

           

          Your calculated field will need to be of Date Type.

           

          [font="courier"]ctod(str(year(userdate))"/"str(month(userdate))"/"str(if(month(userdate).in.(09,04,06,11),30,if(month(userdate)=2,28,31))),"y/m/d")[/font][/quote]Where "userdate" is your input field. (If you set up a Runtime Parameter field with this field name you can test for a number of dates relatively easily.

           

          Don't forget you will need something in there to deal with leap years.

           

          Does this help at all?

           

          Grant

          • Determining Last Day of the month
            Balty G

            Grant

            Thanks for this, just tried it and it seems to be working. Will look at leap year problem later today when I have some time but this gets me back moving again.

             

            Got version 9 installed on another machine. Not used it as yet but will have a look at the solution you suggested for this version later in week.

             

            Thanks again for helping me on my first post.

             

            Balty

            • Determining Last Day of the month
              Grant Perkins

              Originally posted by Balty G:

              Got version 9 installed on another machine. Not used it as yet but will have a look at the solution you suggested for this version later in week.

               

              /b[/quote]Balty,

               

              That sounds like good news.

               

              Just in case you have not found out so far, V9 allows you to open a model without opening a report or database. The 'default' User Defined Functions model will also be opened if the default is set and has not been changed. So you can look at the UDF's very easily. There are quite a number relating to work with dates.

               

              Moreover the new Standard Function - DateAdjust - should offer an easy way to get what you want. In shorthand something like

               

              Add one month to the date you have.

              Deduct the number of days of the month as extracted from the date.

               

              So Version 9 allows this;

               

              [font="courier"]DateAdjust(date,0,1,)-day(date)[/font][/quote]Which can also be written as;

               

              [font="courier"]DateAdjust(date,0,1,-day(date))[/font][/quote]Which is quite neat I think.

               

              Meantime here is an updated formula that should cover leap years for the foreseeable future as far as I am aware.

               

              [font="courier"]ctod(str(year(userdate))"/"str(month(userdate))"/"str(

              if(month(userdate).in.(09,04,06,11),30,

              if(month(userdate)=2 .And. Mod(Year(userdate),4)=0,29,

              if(month(userdate)=2 .And. Mod(Year(userdate),4)<>0,28,31)))),

              "y/m/d")[/font][/quote]I have attempted to present the lines in a way that more clearly identifies the nested IF() formula that deals with the number of the last date of the month. Basically the February thing says that if the numeric year value is divisible by 4 with no remainder then the last day is 29 otherwise it is 28.

               

              There is likely to be a more elegant approach but this should work.

               

              HTH.

               

               

              Grant