Problem using IF statement with dates as part of the formula
 

Hello, I'm trying to figure out why my current formula is not coming up with the correct result. I have a list of dates in column A:
02/15/2013
01/05/2013
03/01/2013
etc.

I would like column B to reference the corresponding cells in column A and populate the correct Quarter for which the date falls into (Qtr1, Qtr2,etc).

My current formula looks like this:
=IF(A1<4/1/2013,"Qtr1",IF(A1<7/1/2013,"Qtr2",IF(A1<10/1/2013,"Qtr3","Qtr4")))

The value that it gives me, regardless of the date in column A is Qtr4.
I'm sure there is something obvious that I am doing incorrectly.
Thanks in advance to anyone that can help!

Dates in formulas don't work that way. You'd need to specify it as a date, then describe the year, month and day to get it to work.

An easier way would be to replace the dates in your formula with the 5digit numbers that Excel uses to interpret dates or put the dates in cells elsewhere and refer to those.

If you post an example workbook I'll happily show you how.

It may be that there's actually a better way to accomplish this than with a nested IF.

S.

Hi S,
I'm going to try and attach a workbook with showing some dates and the way it's organized.
Thanks!

Problem using IF statement with dates as part of the formula
 

Hi Izzy,

Am Thu, 7 Mar 2013 18:06:22 +0000 schrieb Izzy604:

Hello, I'm trying to figure out why my current formula is not coming up
with the correct result. I have a list of dates in column A:
02/15/2013
01/05/2013
03/01/2013
etc.

I would like column B to reference the corresponding cells in column A
and populate the correct Quarter for which the date falls into (Qtr1,
Qtr2,etc).

in B1 try:
="Qtr"&MONTH(MONTH(A1)&0)


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Problem using IF statement with dates as part of the formula
 

"Izzy604" wrote:
My current formula looks like this:
=IF(A1<4/1/2013,"Qtr1",IF(A1<7/1/2013,"Qtr2",IF(A1<10/1/2013,"Qtr3","Qtr4")))

Try:

=IF(A1<--"4/1/2013","Qtr1",IF(A1<--"7/1/2013","Qtr2",
IF(A1<--"10/1/2013","Qtr3","Qtr4")))

Without double-quotes, 4/1/2013 is interpreted as a numeric expression: 4
divided by 1 divided by 2013.

But with double-quotes, "4/1/2013" is simply text, which Excel does not
interpret.

The double negative (--) or any arithmetic operation causes Excel to try to
interpret the text as a numeric input, just as if you had typed into a cell.

However, as a matter of "good practice", the following is better because it
is independent of regional differences, which really matters only if you
might share the Excel file with others:

=IF(A1<DATE(2013,4,1),"Qtr1",IF(A1<DATE(2013,7,1), "Qtr2",
IF(A1<DATE(2013,10,1),"Qtr3","Qtr4")))

Finally, note that your formula only works for 2013. More generally:

=IF(MONTH(A1)<4,"Qtr1",IF(MONTH(A1)<7,"Qtr2",IF(MO NTH(A1)<10,"Qtr3","Qtr4")))

A million thanks joe! It works! And - thanks for clarifying why my original formula was not yielding the correct result.

Thanks for the tip on the formula Claus - it worked too! Just a questions, would you be able to interpret for me what the formula 'says'? It looks very simple, however I'm having trouble understanding how it works and would love to know for my own future reference.
Thank you!!