IF and AND formula for date field

M

murkaboris

Hello:

I have a column that has dates in it in the format of "MM/DD/YY".
I'd like to create a formula that would evaluate this column and return a
value based on the following conditions.

If, lets say "B2" (the date) is less than or equal to DATEVALUE("01/02/09")
and more or equal to DATEVALUE("01/01/10") return the year value of teh "B2"
cell.
Not sure if there is a way but the rest of the dates fall withing 2009 for
which I need to return the year and quarter...ie. 2009 Q1; 2009 Q2 thorugh
Q4. It might not work all within one formula but as far as I get the first
part that would help. I can just manually do the 2009 then.

Thank you.
Monika
 
J

jasontferrell

Try this:
=IF(B2<DATEVALUE("01/02/2009"),YEAR(B2),IF(B2>DATEVALUE
("01/01/2010"),YEAR(B2),"Q"&(INT(MONTH(B2)/4)+1)))
 
M

murkaboris

Hello:

didn't work, it didn't give me any error just showed the formula as typed
in...
Please advise.
Thank you.
Monika
 
F

Fred Smith

Did you put an equal sign in front of the formula? Is the cell formatted for
text? Either of these would cause the problem you described.

It works for me when I test it, so the problem has to be operator error.

Regards,
Fred
 
M

murkaboris

Sure was me, the whole column was formatted as text.
Thank you.

One more adjustment if its possible, for 2009 it returns Q2, Q3, Q4 instead
of 2009 Q2, 2009 Q3...is there a way to adjust the last leg to return the
year and the quarter instead of just the quarter?

Please advise.
Thank you.
Monika
 
J

jasontferrell

Yes, this will do it.
=IF(B2<DATEVALUE("01/02/2009"),YEAR(B2),IF(B2>DATEVALUE
("01/01/2010"),YEAR(B2),"2009Q"&(INT(MONTH(B2)/4)+1)))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top