Excel Year Date

[jamada]

What I would like to do is create an IF statenent contingent upon the YEAR which is with in a date, the month and date are really have no concern.

Either D or E (or both) will have a date within the cell.

So far this is what I have BUT I know its wrong after E3=

IF(D3+OR(E3=DATE(YEAR(1991),"",1))))

tks gg

 

[erik.van.geit]

Hi,

no sure what you mean
is this a solution ?

  D        E        F  G    
1 15/12/06 10/04/05    1991 
2 14/05/91 15/05/91 1       
3 15/12/06 15/12/06         
4 16/05/91 18/10/07 1       
5 16/12/06 16/05/91 1       
6 17/12/06 26/04/10         
7 18/12/06 27/02/11         

test
[Table-It] version 06 by Erik Van Geit

RANGE FORMULA (1st cell)
F1:F7 =IF(OR(YEAR(D1)=$G$1,YEAR(E1)=$G$1),1,"")

[Table-It] version 06 by Erik Van Geit

kind regards,
Erik

 

[jamada]

D E F G
1 15/12/06 10/04/05 1991
2 14/05/91 15/05/91 1
3 15/12/06 15/12/06
4 16/05/91 18/10/07 1
5 16/12/06 16/05/91 1
6 17/12/06 26/04/10
7 18/12/06 27/02/11

AH! A pictures worth a thousand words!

Using your ilustration this is how I would like the end result to look
With the formula inserted in Column F

D E F
1 15/12/06 10/04/05 1
2 14/05/91 15/05/91
3 15/12/06 15/12/06 1
4 16/05/91 18/10/07
5 16/12/06 16/05/91
6 17/12/06 26/04/10 1
7 18/12/06 27/02/11 1


Tks g

 

[erik.van.geit]

Hi,

to get correct allignment of your samples use the tools made for the purpose: for this simple table check out my signature

you want the inverse of what I posted: what did you try ??
my first simple thought would be to switch the "" with the 1
does that work for you ?

=IF(OR(YEAR(D3)=$G$1,YEAR(E3)=$G$1),"",1)

 

[jamada]

Thanks Eric.......BUT the only year on the sheet will be indicated In either Columns E Or F,

What I would like Column F to contain is the number "1" IF the year 1991 is indicated in the adjacednt columns E and or F


Hope this helps you help me!

Thanks

g

 

[barry houdini]

Erik is assuming you have the year to check for in G1, if you just want the year "hard-coded" into the formula use this formula in F1

=IF(OR(YEAR(D1)=1991,YEAR(E1)=1991),"",1)

copied down

 

[jamada]

Egad!..........I've spent hours trying to figure that out.....it works PERFECT, now all I have to do is figure out the rest of the formula.............BUT as Arnold Shwart. all ways said "I'll be back"


Thnaks much for the help Erik and Barry


gg

 

[jamada]

Erik


I really like your "table it" add in, Wow thnaks so much for the help
g

 

[erik.van.geit]

Erik


I really like your "table it" add in, Wow thnaks so much for the help
g

Thank you for the compliment
I saw http://www.mrexcel.com/board2/viewtopic.php?p=1131720
heavy-duty-formulas ... pfffffff !!

 

[jamada]

Certainly Heavy duty for me, everything in Excel for me is self taught, so a lot of trial and erorr!

tks g