Excel 2002 date formulas problem

[Andrew Warren]

Hi all. I have an Excel spreadsheet which I am having a
small problem with. The part I am having trouble with
has 2 columns. The first column is empty and is for
entering birth dates into. The second column has this
formula in cell F2 - I have dragged the formula downwards
from there with the fill handle:

=IF(MONTH(F2)=MONTH($G$1),"B'day this month","")

Cell G1 has this formula in it, which gives that cell the
value of the system date, and displays it in that cell:

=TODAY()

The idea is for the text, "B'day this month," to appear
in the second column if the birthdate entered in the
first column falls within the month displayed in cell
G3. It all works if there are dates entered in the first
column, but if there is no date entered in a cell in the
first column, then, "B'day this month," is appearing next
to it.

I have cell G1 and column F formatted as date category,
in the type *14/03/2001

Andy

[mangesh_yadav]

use:
=IF(AND(ISNUMBER(F2),MONTH(F2)=MONTH($G$1)),"B'day this month","")

- Mangesh

[Harald Staff]

Hi Andy

It's go away in february <g>
If you can't wait, try something like
=IF(AND(MONTH(F2)=MONTH($G$1),F2>1000),"B'day this month","")

HTH. Best wishes harald


"Andrew Warren" <[email protected]> skrev i melding
news:[email protected]...
> Hi all. I have an Excel spreadsheet which I am having a
> small problem with. The part I am having trouble with
> has 2 columns. The first column is empty and is for
> entering birth dates into. The second column has this
> formula in cell F2 - I have dragged the formula downwards
> from there with the fill handle:
>
> =IF(MONTH(F2)=MONTH($G$1),"B'day this month","")
>
> Cell G1 has this formula in it, which gives that cell the
> value of the system date, and displays it in that cell:
>
> =TODAY()
>
> The idea is for the text, "B'day this month," to appear
> in the second column if the birthdate entered in the
> first column falls within the month displayed in cell
> G3. It all works if there are dates entered in the first
> column, but if there is no date entered in a cell in the
> first column, then, "B'day this month," is appearing next
> to it.
>
> I have cell G1 and column F formatted as date category,
> in the type *14/03/2001
>
> Andy

[Chris Ferguson]

How about testing to see if F2 has a value entered into it before doing the
calculation?

something like:-
=IF(F2<>"",IF(MONTH(F2)=MONTH($G$1),"B'day this month",""),"")

Chris

"Andrew Warren" <[email protected]> wrote in message
news:[email protected]...
> Hi all. I have an Excel spreadsheet which I am having a
> small problem with. The part I am having trouble with
> has 2 columns. The first column is empty and is for
> entering birth dates into. The second column has this
> formula in cell F2 - I have dragged the formula downwards
> from there with the fill handle:
>
> =IF(MONTH(F2)=MONTH($G$1),"B'day this month","")
>
> Cell G1 has this formula in it, which gives that cell the
> value of the system date, and displays it in that cell:
>
> =TODAY()
>
> The idea is for the text, "B'day this month," to appear
> in the second column if the birthdate entered in the
> first column falls within the month displayed in cell
> G3. It all works if there are dates entered in the first
> column, but if there is no date entered in a cell in the
> first column, then, "B'day this month," is appearing next
> to it.
>
> I have cell G1 and column F formatted as date category,
> in the type *14/03/2001
>
> Andy

[yaabaa]

Thanks very much guys - both of your suggestions seem to work perfectly!!
Harald, why do you think that my original formula won't work until February?

"Chris Ferguson" wrote:

> How about testing to see if F2 has a value entered into it before doing the
> calculation?
>
> something like:-
> =IF(F2<>"",IF(MONTH(F2)=MONTH($G$1),"B'day this month",""),"")
>
> Chris
>
> "Andrew Warren" <[email protected]> wrote in message
> news:[email protected]...
> > Hi all. I have an Excel spreadsheet which I am having a
> > small problem with. The part I am having trouble with
> > has 2 columns. The first column is empty and is for
> > entering birth dates into. The second column has this
> > formula in cell F2 - I have dragged the formula downwards
> > from there with the fill handle:
> >
> > =IF(MONTH(F2)=MONTH($G$1),"B'day this month","")
> >
> > Cell G1 has this formula in it, which gives that cell the
> > value of the system date, and displays it in that cell:
> >
> > =TODAY()
> >
> > The idea is for the text, "B'day this month," to appear
> > in the second column if the birthdate entered in the
> > first column falls within the month displayed in cell
> > G3. It all works if there are dates entered in the first
> > column, but if there is no date entered in a cell in the
> > first column, then, "B'day this month," is appearing next
> > to it.
> >
> > I have cell G1 and column F formatted as date category,
> > in the type *14/03/2001
> >
> > Andy
>
>
>

[Harald Staff]

"yaabaa" <[email protected]> skrev i melding
news:[email protected]...
> Thanks very much guys - both of your suggestions seem to work perfectly!!
> Harald, why do you think that my original formula won't work until
February?

Sorry for the typo. I ment to say that the error will go away in february
all by itself. Month(0) is 1. So
Month(0) = Month(Date)
is true only in january.

HTH. Best wishes Harald