4 Replies Latest reply on Mar 15, 2013 12:41 PM by Christian

    Date calculation if end date is not always used

    Christian

      Title

      Date calculation if end date is not always used

      Post

           Hi Everyone,

           I am trying to calulate two date fields "Start Date" and "End Date".  I've been using

           Year ( Get (CurrentDate) ) -

           Year ( Start Date )
            
           to tell me how many years someone has been working with us.  Ideally, what I want to calculate is how many years and months someone has been working with us.  The "End Date" however, is not always a used field in cases where the employee is still working for us.
            
           How would I set up this calculation to calculate in Years and Months:
            
           Current Employee: how long they have been with us (End Date is blank)?
           Past Employee: how long they have been with us (End Date is filled out)?
            
           Thanks for your help!

        • 1. Re: Date calculation if end date is not always used
          schamblee

               You will have three fields.  StartDate - Date ,EndDate - Date ,TimeWorked Calulation return text.

               Use this calculation. 

               If(IsEmpty(EndDate);Year ( Get ( CurrentDate ) ) - Year ( StartDate ) - If ( Get ( CurrentDate ) < Date ( Month (StartDate ) ; Day ( StartDate) ; Year ( Get ( CurrentDate ) ) ); 1 ; 0 ) & " Year(s) " &
                  Mod ( Month ( Get ( CurrentDate ) ) - Month ( StartDate ) + 12 - If ( Day ( Get ( CurrentDate ) ) < Day ( StartDate ) ; 1 ; 0 ) ; 12 ) & " Month(s) ";
                  Year ( EndDate ) - Year ( StartDate ) - If ( EndDate < Date ( Month (StartDate ) ; Day ( StartDate) ; Year ( EndDate ) ); 1 ; 0 ) & " Year(s) " &
                  Mod ( Month ( EndDate ) - Month ( StartDate ) + 12 - If ( Day (EndDate ) < Day ( StartDate ) ; 1 ; 0 ) ; 12 ) & " Month(s) "
               )

          • 2. Re: Date calculation if end date is not always used
            Christian

                 Oh wow!  Thank you.  That calculation is much more elaborate than I would have imagined.  Thank you so much for your help!  It works great except for when the End Date field is empty (i.e. the employee is still with us).

                 Let's say one of our employees started Jan 1, 2009, and the return I get is this: 

                 -2009 Year(s) 10 Month(s)

            • 3. Re: Date calculation if end date is not always used
              schamblee

                   I would guess something didn't get copied and pasted correctly on your end.  I have tested and I get  4 years(s) 2 month(s).  I copied the above calculation and pasted into a new test db, to besure I didn't paste something wrong.  Copy from the if to the last ).   Verify any changes you may have made to the calculation to match your field names.  This calculation uses complete dates mm/dd/yyyy.

              • 4. Re: Date calculation if end date is not always used
                Christian

                     Thanks again!  Everything works now.  Not sure what I left out last time.

                     Again, I appreciate your help!