If new date is greater than predetermined date, use predetermined date

Not an excel junkie at all . In the attached sheet under Type of Expiration drop list; "Stipulated" is expiration dates read off the bottle so that is typed in as is, if it's "Absent" then 7 years is assigned from date received. Well and good, but I need help with "Issued". "Issued" is supposed to be 5 years from today/day selected/day opened but not beyond 7 years from date received (Only applies to item that were previously tagged "Absent"? Any assistance will be appreciated. Thanks.

So you're focusing on this section of your uploaded file?

	G	H	I
5	Expiration Date (Absent)	EXP/OK?	Batch No
6	NA	OK
7	12/2/2016	OK
8	FALSE	OK
9	9/25/2016	OK
10	1/17/2014	EXP
11

You might have to "draw" me a timeline.

Not sure if this helps but add another if statement to column G
=IF(C8="Issued",(DATE(YEAR(D8)+5,MONTH(D8),DAY(D8))),IF(C8="Absent",(DATE(YEAR(D8)+7,MONTH(D8),DAY(D8))),IF(C8="Stipulated","NA")))
This should give the 5 years.

Then look at adding another if statement to column H to check if it is over 7 years.

Yes FlameRetired, am focusing on that section. Windy58, that statement is only correct if adding 5 years from today for "Issued" is allowed to go beyond the 7 years from "Absent". Expiration dates for "Issued" are + 5yrs from today but cannot jump over 7 yrs from date received. So the column G dates have a 7 yr limit from column D.

Originally Posted by daddylonglegs

You can perhaps use EDATE to add years and shorten the formula a little.......then this version should do what you want

=IF(C6="Absent",EDATE(D6,12*7),IF(C6="Stipulated","NA",IF(C6="Issued",MIN(EDATE(TODAY(),12*5),EDATE(D6,12*7)))))

If C6 = Issued the formula calculates the earliest date out of TODAY+5 years and D6+7 years

Thanks a lot, this solves it.