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

# Date calculation if end date is not always used

### 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)?

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

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

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

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

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