# Excel Year Date

##### Active Member
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

Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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

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

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)``

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

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

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

Erik

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

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

tks g

Replies
3
Views
76
Replies
10
Views
230
Replies
4
Views
423
Replies
3
Views
336
Replies
11
Views
831

1,218,942
Messages
6,145,352
Members
450,610
Latest member
TheEnginerd

### 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.

### Which adblocker are you using?

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

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