Formula that if the criteria is met add 'for example' a year to a certain date

[Ben2487]

Basically i'm doing a spread sheet for vaccinations and would like it when i enter the date of what injection has been issued it automatically put the date to be renewed in another column, I've had a go but i am struggling

=IF(AND(D3="DTPP",E3="1"),A3=DATE(YEAR(A3)+10,MONTH(A3)+0,DAY(A3)+0))

So basically 'D3' is the injection and 'E3' will be the stage (1,2,3,B) so every injection at each stage will have different renewal dates so i am tryin to get it so that both critea is met take the date out of 'A3' and add so many days,months, years etc out and paste the answer in 'F3'

Im prob way out with the formula so any help much appriecated

Cheers

[gak67]

I would suggest a lookup table with the number of months until the next injection with vaccination type in column A and stage in row 1, as per below

	1	2	3	B
DTPP	1	3	12	12
MMR	2	2	2	0
Hep C	12	12	12	120

You could then have a formula such as:

Please Login or Register  to view this content.

[Ben2487]

After a bit of playing about finally got it to work, for some reason excel was automatically putting a * between the match's

Thanks very much

Ben

[Ben2487]

Hi again,

I was told later that the rabies jab 2 is after week of the first one and rabies jab 3 if 6 weeks after that, I think EDATE only does months is there another date formula which would do weeks or even days and i could perhaps apply it just to the rabies jab??

Many Thanks

Ben

[gak67]

Try this and put the number of days in for the rabies shots.

Please Login or Register  to view this content.

[Ben2487]

Gak67

Worked great thanks a lot im really learning a lot here, I know what i want to achive in formulas its just using the right termanoligy

Ben