6 Replies Latest reply on Sep 5, 2011 3:14 AM by akton

# Calculating Dates between Events

### Title

Calculating Dates between Events

### Post

I would like to be able to calculate the duration in days and months between two events. I created a field called hire date and configured it as date. I created another field and called Today's Date. I would like to see todays date at that field ( but has been unable). I created a third field called Duration with the company and made it a calculation date. Is there a way I can get the duration in years, months and days displayed in this last field. I tried many things but have been unsuccesful.

Thanks to all

• ###### 1. Re: Calculating Dates between Events

You shouldn't have to create a separate field for Today - you can use the Get ( CurrentDate ) function instead in the end calculation.  Make sure that any calculation it is used in is set to be 'Unstored'.

A rough go at working out the years, months, and days between two dates, Date1 and Date 2 (in your case it looks like Date2 would be Get ( CurrentDate ) ) is:

Years:

Case (

DayOfYear ( Date2 ) ≥ DayOfYear ( Date1 ) ; Year ( Date2 ) - Year ( Date1 ) ;

Year ( Date2 ) - Year ( Date1 ) - 1

)

Months:

Case (

Month ( Date2 ) ≥ Month ( Date1 ) and Day (Date2 )  ≥  Day ( Date1 ) ;
Month ( Date2 ) - Month ( Date1 ) ;

Month ( Date2 ) ≥ Month ( Date1 ) and Day (Date2 )  <  Day ( Date1 ) ;
Month ( Date2 ) - Month ( Date1 ) - 1 ;

Month ( Date2 ) < Month ( Date1 ) and Day (Date2 )  ≥  Day ( Date1 ) ;
12 - Month ( Date1 ) +  Month ( Date2 ) ;

12 - Month ( Date1 ) +  Month ( Date2 ) - 1

)

Days:

Case (

Day ( Date2 ) ≥ Day ( Date1 ) ; Day ( Date2 ) - Day ( Date1 ) ;

DayOfYear  ( Date ( Month ( Date2 ) ; "1" ; Year ( Date2 ) ) )

-

DayOfYear  ( Date ( Month ( Date1 ) ; Day ( Date1 ) ; Year ( Date2 )  ) )

)

Note that I haven't checked for Date2 being *before* Date1, for example.  And no, I haven't fully tested this, but it should get you going on the right lines.

• ###### 2. Re: Calculating Dates between Events

Thanks

• ###### 3. Re: Calculating Dates between Events

The eassiest way to calculate the days between two date is: Date1 - Date2  .... the result is the number of days.

If you wish that to see a a kind of date, add getasdate() to the formula: getasdate( Date1 - Date2 )

The latter formula has a few flaws because it projects the outcome of the first formula on the beginning of the gregorian calander. The correct total lenght of months causes possibly a shift between -3 and +3 days. If that's a problem you should calculate more exact with:

Let([

yd=y2-y1;md=m2-m1;dd=d2-d1];

Date ( md ; dd ; yd ) )

(Neat behaviour by the date-function is that an input like: Date ( 5 ; -1 ; 2011 ) will put out: april 30, 2011)

regards, Menno

• ###### 4. Re: Calculating Dates between Events

If you wish to display it as:

6 years, 3 months, 3 days or 2 months, 1 day then you can try this calculation (result is text), set to unstored:

Let ( [
start = YourDate ;
next = Date ( Month ( start ) + 1 ; Day ( start ) ; Year ( start ) ) ;
end = Get ( CurrentDate )  ;
x = 12 * ( Year ( end ) - Year ( start ) ) + Month ( end ) - Month ( start ) - ( Day ( end ) < Day ( start ) ) ;
y = Div ( x ; 12 ) ;
m = Mod ( x ; 12 )  ;
d = Case ( Day ( start ) > Day ( end )  ; Day ( next - Day ( next ) ) +  Day ( end )  - Day ( start )  ; Day ( end ) - Day ( start ) ) ;

string = Case ( y ; y & " year" & Case ( y > 1 ; "s" ) & ", " ) & Case ( m ; m & " month" & Case ( m > 1 ; "s" ) & ", " ) & Case ( d ; d & " day" & Case ( d > 1 ; "s" ) )
] ;
LeftWords ( string ; Length ( string ) )
) // END LET

Substitute 'YourDate' for hire date.  And delete the field Today using instead Get ( CurrentDate ) .

• ###### 5. Re: Calculating Dates between Events

It worked great. The only problem is it shows in the record as 564.00. But when I click with the mouse in the field then it will show as 5 years, 6 months, 4 days. So the calculation and the information is correct, I just need to find out a way to let it display the results without having to click in the field.

Thank you!

• ###### 6. Re: Calculating Dates between Events

I got it . I formatted the appearance of the field in inspector to general. It used to be decimal and every thing is just perfect. Thanks again