Subtracting months formula

[ss]

To explain what I doing it is to do with passports ie you are required
to have 6 months expiry date left before entereing country.

So in cell A1 I have my expiry date, say 15/08/2012
Is there a formula that will deduct 6 months off this date? say in cell B1

thanks

 

[joeu2004]

So in cell A1 I have my expiry date, say 15/08/2012
Is there a formula that will deduct 6 months off this date?
say in cell B1

Enter the following formula in B1:

=EDATE(A1,-6)

If you get #NAME error and you are using Excel 2003, see the EDATE for
instructions for enabling the ATP.

 

[ss]

Enter the following formula in B1:

=EDATE(A1,-6)

If you get #NAME error and you are using Excel 2003, see the EDATE for
instructions for enabling the ATP.

Thanks for quick response.

Yes that works but does raise another question.
As many people will be using this and some are likely to have office
2003 will this show an error on their sheet, if so is there another way
that would cover various versions of office.

 

[joeu2004]

Yes that works but does raise another question.
As many people will be using this and some are likely to
have office 2003 will this show an error on their sheet,
if so is there another way that would cover various versions
of office.

Most people have the ATP, so it should not be a big deal for them to enable
it.

Alternatively, the following is a partial solution:

=DATE(YEAR(A1),MONTH(A1)-6,DAY(A1))

The problem arises when the date in A1 is a day that is not in the month 6
months earlier.

The problem dates (in A1) are: 31st of Mar, May, Oct, Dec; Aug 30 and 31;
and Aug 29 in non-leap years.

There are different opinions about the "right" answer in those cases. Also,
it depends on your purprose.

Try both EDATE and DATE with the dates above in A1. Are you happy with the
DATE result?

-----
Note: Those are the problem dates specifically for a delta of -6 months.
More generally, the problem days of the month are 29 through 31.

The following provides EDATE functionality without relying on the ATP:

=IF(MONTH(DATE(YEAR(A1),MONTH(A1)-6,DAY(A1))<>MONTH(DATE(YEAR(A1),MONTH(A1)-6,1)),
DATE(YEAR(A1),MONTH(A1)-5,0),DATE(YEAR(A1),MONTH(A1)-6,DAY(A1)))

 

[joeu2004]

=IF(MONTH(DATE(YEAR(A1),MONTH(A1)-6,DAY(A1))<>MONTH(DATE(YEAR(A1),MONTH(A1)-6,1)),
DATE(YEAR(A1),MONTH(A1)-5,0),DATE(YEAR(A1),MONTH(A1)-6,DAY(A1)))

A typo. That should be:

=IF(MONTH(DATE(YEAR(A1),MONTH(A1)-6,DAY(A1)))<>MONTH(DATE(YEAR(A1),MONTH(A1)-6,1)),
DATE(YEAR(A1),MONTH(A1)-5,0),DATE(YEAR(A1),MONTH(A1)-6,DAY(A1)))

 

[ss]

A typo. That should be:

=IF(MONTH(DATE(YEAR(A1),MONTH(A1)-6,DAY(A1)))<>MONTH(DATE(YEAR(A1),MONTH(A1)-6,1)),

DATE(YEAR(A1),MONTH(A1)-5,0),DATE(YEAR(A1),MONTH(A1)-6,DAY(A1)))

Thanks again, I will work on both and decide on best option.

The reason I am doing this is because the people concerned are having
difficulty working out if their passports will be valid (ie still have 6
months on it) so the chances of them enabling anything are even less
slim, so just trying to cover best option for them.