Date return based on the earlier of 2 other dates

Morning all

I don't know if this can be done, but here goes:

I have a set of 2 annual check dates that are manually entered. Through the year, the person needs to have regular training every 3, 6 and 9 months from that date. What I need is that the cells showing the training dates needs to adjust and display the correct date for the check that is first.

So in the attached example I need the dates in columns E,G and I to look at columns B and C, work out which date comes soonest and then display the correct info in the empty cells. I have manually entered the date so you can see what I need displayed.

Looking at B5 and C5, the date in E5 would need to work out that B5 comes first so it would then return 9 months from that date, G5 would give a date 6 months prior to that date and I5 would give a date 3 months prior to that date.

What this would mean is that when this person has their check on 1 August, and we enter the date for their check next year, the 3 training columns will let us know what dates their next batch of training is due.

Hi CTC operations,

You can use below formula:-
=DATE(YEAR(MIN($B5,$C5)),MONTH(MIN($B5,$C5))+E$2,DAY(MIN($B5,$C5)))
where E2 is just a reference to 3,6,9

see attached file;- sample dates.xls

Regards,
DILIPandey

<click on below 'star' if this helps>

hi CTCoperations, i remember the last post you do not have EOMONTH available? so did you do the add-in? here's what you can use if you did:
=EOMONTH(MIN($B5:$C5),-10)+1

=EOMONTH(MIN($B5:$C5),-7)+1

=EOMONTH(MIN($B5:$C5),-4)+1

and if you havent done the add-in, here's how it can be done:
http://chandoo.org/forums/topic/exce...-eomonth-v2007
havent tried it myself because i do not have Excel 2003. hope it works for you