4 Replies Latest reply on Mar 4, 2014 11:58 AM by lijnbach

    Calculating end of moth in case month is e.g. 28, 20 or 30 days

    lijnbach

      Title

      Calculating end of moth in case month is e.g. 28, 20 or 30 days

      Post

           I have employee records. In the record is a field date "Date1" This is used for the date an employee started working for a company.

           The second field is a field with "Number of Months" for the temporary contracts. I calculate the end of the contract, so the HRM Manager gets an alert before the contract ends. This calculation is not the problem. The problem rises when someone is hired on e.g. August 31. If this person is hired for 6 months, the calculated field returns March 03 instead of February 28.

           This is what I use in the calculated field:

           Date ( Month (Date1)  + Number of Months; Day ( Date1); Year ( Date1))

           I can't find a solution for this, but that has (I think) a lot to do with our Dutch translations.

           Can someone help me out?

           Thanks,

           Hans Lijnbach

        • 1. Re: Calculating end of moth in case month is e.g. 28, 20 or 30 days
          philmodjunk

               Let ( [ D = Date ( Month (Date1)  + 6; Day ( Date1) ; Year ( Date1)) ;
                          adj = min ( day ( Date ( Month ( D ) + 1 ; 0 ; Year ( D ) ) ) ; Day ( Date1 ) )
                       ];
                         D + Adj - 1
                      )

          • 2. Re: Calculating end of moth in case month is e.g. 28, 20 or 30 days
            lijnbach

                 O.K. Thanks Phil,

                 Something is not working. But I think I did not explain myself in the right way. With your script I get the correction for the end date in case of 31 and 30, but I get one month to much.

                 Let's say I hire someone on December 31 in 2014. The contract ends in that case on December 31 in 2015. In your script it ends one month later (January 30 from 2016).

                 And am I right that it is not working for the month February? 

                 And I keep struggling with our translations. All the script steps in Help are translated in Dutch, but the real script has to be build in English. And although my native language is Dutch, I am more used to English in case of information technology.

                 Hans Lijnbach

            • 3. Re: Calculating end of moth in case month is e.g. 28, 20 or 30 days
              philmodjunk

                   It should also work for February.

                   Day ( Date ( Month ( D ) + 1 ; 0 ; Year ( D ) ) )  returns the number of days in the month of D. (The calc goes one month into the future, but then backs up to day 0 to get the date of the last day of the preceding month.)

                   The min function then selects which ever value is smaller, the day of Date1 or the number of days of the new date's month.

                   But I did make an error that I missed when testing this in the data viewer. It should be:

                   Let ( [ D = Date ( Month (Date1)  + 6; 1 ; Year ( Date1)) ;
                              adj = min ( day ( Date ( Month ( D ) + 1 ; 0 ; Year ( D ) ) ) ; Day ( Date1 ) )
                           ];
                             D + Adj - 1
                          )

              • 4. Re: Calculating end of moth in case month is e.g. 28, 20 or 30 days
                lijnbach

                     O.K. Phil,

                     Lots of thanks again. You helped me out again. With your explanation in English I understand what is happening and what you are doing in the script.

                     This works perfect.

                     Hans Lijnbach