Salesforce and other SMB Solutions are coming soon. ×

# Date to Date 'IF' Calculations when field is empty

## Recommended Posts

I have a member file and a building file. I need to calculate how many years a person has been living or renting a house. I have the following calculation for when a persons lease is up.

Date In(Date field), Date Out(Date Field), Nb Yrs (Calculation, Result number)=

If( DayofYear(Date Out) >= DayofYear(Date In),

Year(Date Out) - Year(Date In) ,

Year(Date Out) - Year(Date In) -1)

Works fine. But what do I do when there is no Date Out?

Is there another way to do this?

I love Filemaker, but working with dates are frustrating.

Help anyone.

Thanks

##### Share on other sites

I everyone,

Never mind my last posting I found the solution. Just wasn't thinking clearly.

If( If( IsEmpty(Date Out), DayofYear(Today),DayofYear(Date Out) ) >= DayofYear(Date In),

If( IsEmpty(Date Out), Year(Today),Year(Date Out) )- Year(Date In) ,

If( IsEmpty(Date Out), Year(Today),Year(Date Out) )- Year(Date In) -1)

Thanks anyway.

##### Share on other sites

Hi SuzieQ,

This might be a little simpler for you and a bit more efficient:

Case(IsEmpty(DateOut), (Status(CurrentDate) - DateIn) /365, (DateOut - DateIn) / 365)

It will be off by a day (I believe) if there is a leap year, but since you are rounding to a full year, you wouldn't notice it.

If you would like it to display a bit more accurately than a full year (like total months), you could use:

Round(Case(IsEmpty(DateOut), (Status(CurrentDate) - DateIn) /12, (DateOut - DateIn) / 12), 0)

I used Status(CurrentDate) because Today() will only update if the database is closed and opened again each day. I didn't know if this might be a consideration, but thought I'd mention it.

And I'll bet there are even better (shorter, more efficient) calculations out there!

LaRetta

##### Share on other sites

Hi Suzie, Hi LaRetta,

There is a problem with using either DayOfYear( ) or dividing by '365' in a calculation of this type, because neither takes account of leap years.

In the latter case, where someone has been renting for a couple of decades or more, the result will be off by something of the order of a week, which is unlikely to be acceptable. Dividing by 365.25 and then enclosing the whole expression within the Int( ) function would get closer, but it is still not prescise.

Off the top of my head, I'd be inclined to suggest that you instead exploit the behaviour of the Min( ) function, which igonres null date values, then explicitly force the year comparison so as to accurately handle leap years as either the 'In' year, the 'Out' year or any number of years in between. That would give you a calc along the lines of:

```Year(Min(Date Out, Status(CurrentDate))) - Year(Date In) -
(Date(Month(Min(Date Out, Status(CurrentDate))), Day(Min(Date Out, Status(CurrentDate))), Year(Status(CurrentDate))) <
Date(Month(Date In), Day(Date In), Year(Status(CurrentDate))))```

It will take a little longer to calculate - and will need to be unstored in order to remain up-to-date, but I believe it will be accurate for all cases, which I'd regard as the primary consideration.

If there's a shorter way to do it *accurately*, then I'm afraid it hasn't occurred to me yet...

##### Share on other sites

Thank you for the explanation, Ray.

Doesn't Year() itself properly address Leap Year? Since SusieQ only requires whole years, wouldn't this work also?

Case(not not (DateOut), Year(DateOut) - Year(DateIn), Year(Status(CurrentDate)) - Year(DateIn))

LaRetta

##### Share on other sites

Hi LaRetta,

With the kind of formula you're now suggesting, if a tenant moved in on 31 December, then by the very next morning, your formula would be saying that they had been renting for a year already.

So yes, it would be just 'slightly' less accurate...

##### Share on other sites

Oh. A flaw in my test, I see.

3/16/2001 returned 2.

12/1/2003 returned 0.

12/7/1999 returned 4.

I see my mis-logic in it. The fact that it's now December skew'd my result. :rolleyes: I would need to know how accurate a numeric or date calculation needs to be; in fact, better to just always be extremely accurate and then it's never a concern. Thank you so much for the re-direct.

That's an intriguing calc you provided. I'll figure it out.

LaRetta

##### Share on other sites

Originally posted by LaRetta:

[qb]I would need to know how accurate a numeric or date calculation needs to be...[/qb]

There is a general expectation that date calculations be *exact*. Especially when people's entitlements or liabilities are dependent on them. :rolleyes:
##### Share on other sites

Hi LaRetta & Ray

To answer your question, the purpose of knowing the number of years renting a house, according to the native village I'm preparing this for, after 25 years of rental, they become owners. Therefore I need to keep track on how many year a member is renting a building.

Let me know which solution is required.

I'm still new at this. I appreciate your comments & responses and look forward to more.

##### Share on other sites

Hi SuzieQ,

Without doubt, go with Ray's calculation. You can 'put it in the bank' that it is the best solution out there!

LaRetta

• ### Images

• By Soliant Consulting,
• By Soliant Consulting,
• By Soliant Consulting,
• ### Forum Statistics

• Total Topics
33.7k
• Total Posts
141.6k
×
×
• Create New...