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?

Please Login or Register  to view this content.

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