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
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

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
 

Forum statistics

Threads
1,141,427
Messages
5,706,374
Members
421,446
Latest member
rena jhon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top