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
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.
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?
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?
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.
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.
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
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 DateLet ( [My = ( Year ( Ax Date) - Year ( Date Accepted) ) * 12 ;Adj = Month (Ax Date ) - Month ( Date Accepted )];My + Adj))
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))
Worked like a charm Phil, thank you so much!!