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