Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
"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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
sharing/using/saving Excel 2002 files in Excel 2003 | Excel Discussion (Misc queries) | |||
Problem accessing Oracle Public Synonyms from Microsoft Excel 2002 | Excel Discussion (Misc queries) | |||
Problems with Excel formulas when 2002 upgraded to XP | Excel Worksheet Functions | |||
Excel, problem with hiding formulas.... please help! | Excel Worksheet Functions |