dates

[Twist]

I have a situation where I need to combine 3 fields, day, month, and year. I 've created a calculation to do that, but now need to evaluate if the result is less than or equal 3 months/ equal to 4-7 months/ equal to 7-12 months or greater than 12 months. I have tried several different combinations of calculations, but am not having any success.

[-Queue-]

Is this a date subtraction/addition problem? I don't understand how you could have a month > 12 otherwise. Can you provide more specifics?

[Twist]

Let me try and explain it this way , I ask when does your subscription run out: month-day-year. The range could be fairly different example 1; 3/30/2004 would be less than 3 months, example 2; 12/12/2005 is > 12 months. I need to take the result of my month/day/year and evaluate for time frames 0-3 months, 4-7 months, 7-12 months or > 12 months

[spb]

Is your combined day/month/year data stored in an actual date field? Is there a TextTodate calculation involved? If so, you could make a calc field that displays exactly where a given subscription falls (I'll call your combined date field SubDate), like:

case(SubDate - Status (CurrentDate) < 90, "less than 3 months",

SubDate - Status (CurrentDate) > 120 and SubDate - Status (CurrentDate) < 210, "4-7 months",

SubDate - Status (CurrentDate) > 211 and SubDate - Status (CurrentDate) < 360, "7-12 months",

"greater than 12 months")

I assumed all months are 30 days, so if you need exact to-the-day counts, the calculation would be different. Filemaker will perform the subtraction and give you numbers of days. It will take into account 30, 31 or 28/9 day months when subtracting.

This will give you a field that will display the text in your post for each variation, but you can display whatever text is needed. If you do this, when the field is placed on a layout for reference, format the field to not allow entry. This will avoid the "field is not modifiable" error box if someone accidentally clicks into it.

Steve Brown