2 Replies Latest reply on Jun 8, 2015 6:46 AM by pooja.gandhi

    Calculated field for tenure

    Nadia Garrett

      Hi there,

       

      I'm using the following calculated field to group together the tenure of employees who leave my organisation.

       

      Adjusted service date refers to their start date with the company; action start date refers to their leave date. I get no error message when I do this calculation, however for some reason it seems to be grouping people under 10+ years when they should fall under one of the other tenure bands.

       

      IF DATEDIFF('month',[Census#csv.Adjusted Service Date],[Action Start Date]) <= 6 THEN '0-6 mnths'

      ELSEIF DATEDIFF('month',[Census#csv.Adjusted Service Date],[Action Start Date]) <= 12 THEN '7-12 mnths'

      ELSEIF DATEDIFF('month',[Census#csv.Adjusted Service Date],[Action Start Date]) <= 36 THEN '1-3 yrs'

      ELSEIF DATEDIFF('month',[Census#csv.Adjusted Service Date],[Action Start Date]) <= 60 THEN '3-5 yrs'

      ELSEIF DATEDIFF('month',[Census#csv.Adjusted Service Date],[Action Start Date]) <= 120 THEN '5-10 yrs'

      ELSE '10+ yrs'

      END

       

      Does my calculation look correct? Any tips would be gratefully received.

       

      Many thanks,

       

      Nadia

        • 1. Re: Calculated field for tenure
          Sai Manikanta N

          Hi Nadia,

           

          Your calculated filed looks correct and before using, check date format of both and test results

           

          -

          Sai

          • 2. Re: Calculated field for tenure
            pooja.gandhi

            Hi Nadia,

             

            For the formula to work as expected you will need 'ranges' to accurately drop the employees in the right bucket. So the first part is correct wherein all employees who have been with the orgnization for less than or equal to 6 months will fall into the '0-6 months' range.

             

            For all other months, you will need the range. For example: DATEDIFF('month',[Census#csv.Adjusted Service Date],[Action Start Date]) <= 12  AND DATEDIFF('month',[Census#csv.Adjusted Service Date],[Action Start Date]) > 6 THEN  '7-12 mnths'

             

            and so on.

             

            IF DATEDIFF('month',[Census#csv.Adjusted Service Date],[Action Start Date]) <= 6 THEN '0-6 mnths'

            ELSEIF DATEDIFF('month',[Census#csv.Adjusted Service Date],[Action Start Date]) <= 12  AND DATEDIFF('month',[Census#csv.Adjusted Service Date],[Action Start Date]) > 6 THEN  '7-12 mnths'