10 Replies Latest reply on Dec 23, 2013 8:15 AM by Annette

    Finds Using Elapsed Time

    Annette

      Title

      Finds Using Elapsed Time

      Post

           I have a calculation that returns an elapsed time between two dates in months and days which works just as i want it to.  But the problem is when I'm trying to do a find.  If I wanted to find a record where the elapsed time is between 0 months 0 days and 4 months 0 days how do I got about this?  I will need to do various finds for the following: 

           0 - 4 months 0 days

           4 months 1 day - 8 months

           8 months 1 day - 12 months

           12 months 1 day - 18 months

           18 months 1 day - 24 months

            

           Thanks!! 

        • 1. Re: Finds Using Elapsed Time
          Annette

               I should probably mentioned that a month is the date of one month to the same day of the next month so Jan 1 to Feb 1 returns 1 month 0 days; Feb 20 to March 20 is 1 month 0 days

          • 2. Re: Finds Using Elapsed Time
            philmodjunk

                 I suggest that you set up a field for just the elapsed months and a separate field for the additional elapsed days. You can then use a range to perform a find for records with the elapsed months and then use Constrain Found set to omit any found records where the Elapsed days put them out side of the range.

            • 3. Re: Finds Using Elapsed Time
              Annette

                   Sorry for the delayed response.  Thanks for your help.  I could be being really dim here but I don't follow how to get this to work.  

                   I have two fields, one that I've done a calculation field to get the elapsed months, the other gives me elapsed days (November 1 - December 1 = 30)

                   But not sure how to get this to give me the additional elapsed days.  I'm assuming by that you meant if the date was from Nov 1 to Dec 2 I would get 1 in the month field and 1 in the additional days field?

              • 4. Re: Finds Using Elapsed Time
                philmodjunk

                     Sorry, but I think that I misread your original sample data.

                     I thought that you were calculating the elapsed time as X Months plus Y days as in: "There are 6 Months and 3 days in the interval from date 1 from date 2." My suggestion would put the 6 and the 3 in different fields for such a search and constrain, but now that does not appear to match the sample data that I see or am I misunderstanding?

                • 5. Re: Finds Using Elapsed Time
                  Annette

                       No Phil, you understood my original question.  It's me who didn't explain myself properly or made this more confusing than it really is.  I tend to over-think the calculations and make them more complicating than they need to be.  I didn't realize that it was as easy as making two additional fields with the following calculations:

                       Month ( Field2 ) - Month ( Field1 )   and   Day ( Field2 ) - Day ( Field1)  resulting in 1 in the month field and 1 in the days field if I used Dec 2 - Nov 1.  

                       Now I can apply the constrain found as you suggested.  

                       Originally how I had the days field it was giving me the total number of days between the two dates.   Again, sorry for making it more difficult than it needed to be.  frown

                        

                  • 6. Re: Finds Using Elapsed Time
                    Annette

                         Ok...so it doesn't work as simply as that.  because a date range that should give me 3 (months) 22 (days) is returning 4 and -9.  

                         Sigh.......

                    • 7. Re: Finds Using Elapsed Time
                      philmodjunk

                           Yes, your calculations need to allow for the fact that Date1 and Date2 may be dates with different years.

                           Let ( [My = ( Year ( Date2 ) - Year ( Date1 ) ) * 12 ;
                                     Adj = Month ( Date2 ) - Month ( Date1 )
                                    ];
                                     My + Adj
                                   )

                      • 8. Re: Finds Using Elapsed Time
                        Annette

                             if i wanted to alter that slightly if date 2 (ax date)  was blank to use the current date how would i do so?  i tried the following but it didn't work.  

                              

                             If ( IsEmpty ( Ax Date); Get ( CurrentDate );Ax Date
                              
                             Let ( [My = ( Year ( Ax Date) - Year ( Date Accepted) ) * 12 ;
                                       Adj = Month (Ax Date ) - Month ( Date Accepted )
                                      ];
                                       My + Adj
                                     ))
                        • 9. Re: Finds Using Elapsed Time
                          philmodjunk
                               Let ( [D2 = If ( IsEmpty ( Ax Date ) ; Get ( CurrentDate ) ; Ax date ) ;
                                         My = ( Year ( D2 ) - Year ( Date Accepted) ) * 12 ;
                                         Adj = Month ( D2 ) - Month ( Date Accepted )
                                        ];
                                         My + Adj
                                       ))
                          • 10. Re: Finds Using Elapsed Time
                            Annette

                                 Worked like a charm Phil, thank you so much!!