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

# Finds Using Elapsed Time

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

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

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

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

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

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.

• ###### 6. Re: Finds Using Elapsed Time

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

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 )
];
)

• ###### 8. Re: Finds Using Elapsed Time

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 )
];