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

    Calculating Dates between Events

    akton

      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
          Sorbsbuster

          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
            akton

            LaughingThanks

            • 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([

              oldest=If(Date1<Date2;Date1;Date2);newest=If(Date1<Date2;Date2;Date1);

              y1=year(oldest);y2=year(newest);m1=month(oldest);m2=month(newest);d1=day(oldest);d2=day(newest);

              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
                LaRetta_1

                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
                  akton

                  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
                    akton

                    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