3 Replies Latest reply: May 15, 2014 10:04 AM by jdino73 _ RSS

    Concatenate error

    jdino73 _

      I have a column with Years of Service another with Month of service. I would like to create a calculated field called Lenght of service

       

      I tried this formual but it doesnt seem to work

       

      Concatenate_XL(,"."[monthof service]

       

      Could you please revise.

       

      thank you

        • Concatenate error
          Data Kruncher

          Odds are that and are numeric fields, so you'll need to use the Trim and Str functions along with a + operator:

           

          Concatenate_XL(Trim(Str()),"."+Trim(Str()))

           

          though I haven't tried it out yet myself.

            • Concatenate error
              Olly Bond

              Hello jdino,

               

              ConcatenateXL will give you a joined up string, but this isn't the same as a time interval. For example 1 year and 3 months would appear as 1.3, which numerically is greater than 1.11, but represents a shorter time than 1 year and 11 months.

               

              Concatenate_XL(Trim(Str()),"."+Trim(Str(;2;0;"0")))

              /CODE

               

              would give you at least 1.03 for 1 year and 3 months, and 1.11 for 1 year and 11 months, allowing correct sorting.

               

              If you wanted to be able to perform calculations (totals across multiple staff members in a department, for example) then you might want to explore:

               

              +(/12)

              /CODE

               

              Best wishes,

               

              Olly

              • Concatenate error
                jdino73 _

                Thank you for your help it worked