Excel Year Date

jamada

Active Member
Joined
Mar 23, 2006
Messages
323
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
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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
Code:
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

Active Member
Joined
Mar 23, 2006
Messages
323
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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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 ?
Code:
=IF(OR(YEAR(D3)=$G$1,YEAR(E3)=$G$1),"",1)
 

jamada

Active Member
Joined
Mar 23, 2006
Messages
323

ADVERTISEMENT

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

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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

Active Member
Joined
Mar 23, 2006
Messages
323

ADVERTISEMENT

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

Active Member
Joined
Mar 23, 2006
Messages
323
Erik


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

jamada

Active Member
Joined
Mar 23, 2006
Messages
323
Certainly Heavy duty for me, everything in Excel for me is self taught, so a lot of trial and erorr!

tks g
 

Watch MrExcel Video

Forum statistics

Threads
1,112,796
Messages
5,542,552
Members
410,560
Latest member
1ndependent
Top