Is there a function that would allow me to look at a date and return a
result, say, 'before' or 'after' where the date is compared with a base year
date (30 September 2000) and any date before that date returns a 'before' and
any date (EVEN 1 DAY) after that date will return an 'after'

The formula I've tried is:
Start Date Base year Years' service Before or After?
01/10/2001 30/09/2000 1
=IF(Q33<=0,"BEFORE","AFTER")

where Q33 is the number of years' service.

The problem is of course what happens between 1/10/2000 (1 day AFTER base
year) and 31/12/2000 (end of base year) where only the years are subtracted
in my formula and no account is taken of days.

1/10/2000 will still return a '0' of course but I need a formula that will
count an extra DAY or days after 30/9/2000 and before 1/1/2001 to return an
'AFTER' in this date range.

many thanks

Hi,

From what you describe you are simply trying to establish if a date is
before or after your base date of 30/9/2000. If so you don't need the
intermedate calcilation, simply compare the 2 dates

=IF(O33<P33,"Before","After")

Mike

"Zakynthos" wrote:

> Is there a function that would allow me to look at a date and return a
> result, say, 'before' or 'after' where the date is compared with a base year
> date (30 September 2000) and any date before that date returns a 'before' and
> any date (EVEN 1 DAY) after that date will return an 'after'
>
> The formula I've tried is:
> Start Date Base year Years' service Before or After?
> 01/10/2001 30/09/2000 1
> =IF(Q33<=0,"BEFORE","AFTER")
>
> where Q33 is the number of years' service.
>
> The problem is of course what happens between 1/10/2000 (1 day AFTER base
> year) and 31/12/2000 (end of base year) where only the years are subtracted
> in my formula and no account is taken of days.
>
> 1/10/2000 will still return a '0' of course but I need a formula that will
> count an extra DAY or days after 30/9/2000 and before 1/1/2001 to return an
> 'AFTER' in this date range.
>
> many thanks

If you have a base date in A1 and another date in B1, then:

=IF(A1=B1,"on",IF(B1<A1,"before","after"))

--
Gary''s Student - gsnu200835