Finding a date in the past given a timeframe

[James Gill]

I am trying to find a way to calculate a date in the past given a user defined criteria.

I've searched all over the place but I can't seem to find something that does this. I've found plenty of references for calculating elapsed time between two dates but nothing out there that will find a date in the past given a timeframe.

if ( Date of Service ) > 14 days ; "True" ;

if ( Date of Service ) > 6 months ; "True" ;

if ( Date of Service ) > 1 Years ; "True" ;

"False"

It seems that this would be easy to do given the number of days contained within whatever criteria the user defines but I can't figure it out.

[comment]

I don't think this is clear enough.

[Lee Smith]

Am I missting something here. Since the example you gave is saying that anything less then 15 days is to be False, and everything over the 14 days is to be True, why not;

Case ( Date of Service ≤ Get ( CurrentDate ) +14; "False"; "True")

Lee

[James Gill]

Ah, I don't think I described it clearly enough. The field that contains what I've called "duration" can contain a literal string "days" "months" or "years". It is then up to the user to determine the amount and duration of each record.

The goal of this is to allow the user to create a definable validation that will validate a calculation and show a user if the record is allowable.

To put this into context, these are for vaccination records that are submitted to us. Each vaccination record has what's called a "lifetime" that tells whether or not a vaccination needs to be given.

One vaccination may have a lifetime of 2 years. So if I have a vaccination record that has a date of last service (aka date last given) that is 4 years ago, then I want that vaccination to be required. There are many different variations of lifetimes and vaccinations and so I want the users to be able to define the vaccinations and criteria themselves so a filemaker developer doesn't have to do it in the future.

I hope that explains a little better what I'm trying to accomplish.

Edit: First, the user will need to define the criteria. Because the criteria can be in either a day, month, or year , I need to translate what they have input into a base numerical number.

Year = 365 days (varies depending on leap year)

Month = 30 days (but this changes depending on month)

Day = 1 day

So if they put it "5 years", I need it to spit out the date that would coincide with 5 years prior while accounting for leap years and months that have 29 days or 31 days.

If they put in " 6 months", I need it to calculate the date that would coincide with 6 months prior to the current date and account for actual month length.

All the below criteria are based upon a current date of 12/1/2010

Record 1 

Time Field = 6 

Duration Field = Days

Date Not Valid = 11/25/2010

another record contains:

Record 2

Time Field = 8

Duration Field = months 

Date Not Valid = 4/1/2010

and another:

Record 3

Time Field = 10

Duration Field = years 

Date Not Valid = 12/1/2000

Edited December 1, 2010 by Guest

[comment]

The field that contains what I've called "duration" can contain a literal string "days" "months" or "years".

I would suggest you use TWO fields for this, e.g.:

Duration = 3

Units = "months"

So if they put it "5 years", I need it to spit out the date that would coincide with 5 years prior

This part I still don't get. If you have a date that a vaccination was last administered, and the lifetime of the vaccination is 5 years, shouldn't the result be an expiration date that is 5 years LATER than the given date?

I need to translate what they have input into a base numerical number.

Not really. You can employ a different formula for each type of unit.