Problem using IF statement with dates as part of the formula

[Izzy604]

Hello, I'm trying to figure out why my current formula is not coming u
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
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

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[Spencer101]

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

I would like column B to reference the corresponding cells in column
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
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 th
5digit numbers that Excel uses to interpret dates or put the dates i
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 tha
with a nested IF

S

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[Claus Busch]

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

 

[joeu2004]

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(MONTH(A1)<10,"Qtr3","Qtr4")))

 

[Izzy604]

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 no

interpret.

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

However, as a matter of "good practice", the following is better becaus
it
is independent of regional differences, which really matters only if yo

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(MONTH(A1)<10,"Qtr3","Qtr4")))

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

+-------------------------------------------------------------------
+-------------------------------------------------------------------