Add Years to Date Formula/Calc

[catldogn]

I am FM challenged and would appreciate a shove in the correct direction re: a couple of date formulas/calculations, if possible...

The first is to create a new date field by adding two years to an exisiting date field.

The second is to create a new date field by first searching multiple date fields, confirming all contain a date, then finding the oldest date of the multiple fields and adding two years.

Edited February 20, 2010 by Guest

[Lee Smith]

Hi catldogn, and welcome to the Forum.

I am FM challenged

LOL, aren't we all.

For the first calculation, try this

Date ( Month ( YourDateField ) ; Day ( YourDateField ) ; Year ( YourDateField )+2 )

Not quite clear on what you are envisioning.

Why not view the file in Table View, and sort by the date.

or,

Do a search for a Range of Data on the date, looking for the range that you want.

To add the 2 year requirement, wouldn't the calculation field already have done that?

HTH

Lee

[catldogn]

I'm probably complicating things, to try to clarify - we are tracking search and rescue qualification/expiration dates, which are different for each team member. First, the member must have completed all qualification sections (have a completion date for CPR, First Aid and Survival), then the expiration would equal two years from the oldest date.

Member #1

2/15/08 - CPR

1/13/09 - First Aid

3/23/09 - Survival

Expiration = 2/15/10 (two years from the oldest date of 2/15/08)

Member #2

2/20/10 - CPR

9/21/09 - First Aid

6/15/09 - Survival

Expiration = 6/15/11 (two years from the oldest date of 6/15/09)

Etc.

Edited February 20, 2010 by Guest
To Say Thanks for Helping!

[catldogn]

Or, I could manually enter expiration for each qualification section (enter a date with the two years already added), if there's a way to then search those multiple date fields for the oldest date and grab that date for the new expiration date field? Basically the expiration = the oldest date.

[comment]

If it's always the same three date fields for everyone, try:

Let (

exp = Min ( CPR ; First Aid ; Survival )

;

Date ( Month ( exp ) ; Day ( exp ) ; Year ( exp ) + 2 )

)

[catldogn]

First, thanks for working through this with me, I do truly appreciate the time and effort involved!

Okay, I'm testing away... and have decided that being there's no way around manually adding a date, I may as well manually add the date with the two years already added.

That being done, is there now a way to search date field 1, date field 2 and date field 3, find the oldest date, and put in a new date field?

[comment]

have decided that being there's no way around manually adding a date, I may as well manually add the date with the two years already added.

I am not sure that's a good idea. In any case, to calculate the earliest date out of three, use the Min() function as shown above.

[catldogn]

Super nice! I removed the +2 and it worked perfectly... except, I need to add something that requires a date in all the specified date fields in order to calculate.

[comment]

Why?

[catldogn]

Because, in the case of the following, the formula returns 2/15/08, when I need it to be "nothing" because there is no date for First Aid, meaning the team member has not completed all the required sections.

2/15/08 - CPR

"no date" - First Aid

3/23/09 - Survival

[comment]

Try something like:

Case (

CPR and First Aid and Survival ;

<-- the calc goes here -->

)

[catldogn]

I'm blowing it somewhere...

Case (

BasicCPR and BasicFirstAid and BasicMapComp and BasicRadioCom)

Let (

exp = Min ( BasicCPR ; BasicFirstAid ; BasicMapComp ; BasicRadioCom )

;

Date ( Month ( exp ) ; Day ( exp ) ; Year ( exp ))

[comment]

It should be:

Case (

BasicCPR and BasicFirstAid and BasicMapComp and BasicRadioCom ;

Let (

exp = Min ( BasicCPR ; BasicFirstAid ; BasicMapComp ; BasicRadioCom )

;

Date ( Month ( exp ) ; Day ( exp ) ; Year ( exp ) + 2 )

)

)

But if you're not adding the 2 years, then it can be simply:

Case (

BasicCPR and BasicFirstAid and BasicMapComp and BasicRadioCom ;

Min ( BasicCPR ; BasicFirstAid ; BasicMapComp ; BasicRadioCom )

)

[catldogn]

Thanks so very much... that worked great!