Subtracting months formula

S

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
 
J

joeu2004

ss said:
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.
 
S

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.
 
J

joeu2004

ss said:
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)))
 
J

joeu2004

Errata.... I said:
=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)))
 
S

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top