Excel 2002 date formulas problem
 

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

Hi Andy

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

HTH. Best wishes harald


"Andrew Warren" skrev i melding
...
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

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" wrote in message
...
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

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" wrote in message
...
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" skrev i melding
...
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