dates

This topic is 6542 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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.

Share on other sites

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

Share on other sites

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

Share on other sites

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

Share on other sites

This topic is 6542 days old. Please don't post here. Open a new topic instead.

Create an account

Register a new account