Finding Dates in a range

Hi All,

Forgive me if this has been answered before. I tried to Search but kept getting a blank screen and gave up.

I have a file that has a column of dates (In column E, starting in E2). And need to find everyone that would be getting an Award for service this 4th Quarter. I tried using the formula below but to no avail and not sure why it's not working. Can anyone critique my formula for me?

=IF(AND(OR(RIGHT(YEAR(E2),1)=8,RIGHT(YEAR(E2),1)=3),OR(MONTH(E2)=10,MONTH(E2)=11,MONTH(E2)=12)),"4thQAward","")

Thanks in advance,
Cullen

I don't think you can use RIGHT in conjunction with the YEAR there; try this:

=IF(AND(OR(YEAR(E2)=2008,YEAR(E2)=2003),OR(MONTH(E2)=10,MONTH(E2)=11,MONTH(E2)=12)),"4thQAward","")

I assumed you were looking for 2008 or 2003 based on the formula you had...

RIGHT function always returns a text value so you need to have quotes around the 3 and the 8 like this

=IF(AND(OR(RIGHT(YEAR(E2),1)="8",RIGHT(YEAR(E2),1)="3"),OR(MONTH(E2)=10,MONTH(E2)=11,MONTH(E2)=12)),"4thQAward","")

That will show the award text for any year ending in a 3 or 8 where month is in the 4th quarter. You could also do that like this

=IF((MOD(YEAR(E2),5)=3)*(MONTH(E2)>9),"4thQAward","")

Hi jwhitwell,

Thanks for the help! Your formula works but I'm not only looking for 2008 and 2003, I'm also wanting any year ending in 8 or 3 (1968, 1993... etc).

I can manipulate your formula to work but it's getting kind of messy and if I don't have the year in the Or() part of the if statement I could miss it....

Again thanks for the help, but I think I might be looking at this the wrong way. I'll post back if I can think of a more efficient formula.

If you can think of something let me know!!!

Cullen

Man you guys are quick!
daddylonglegs & jwhitwell, Thanks for the replies and the knowledge. I appreciate the help!!!

Thanks,
Cullen