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)))