Nested IF Function...why isnt it working?

[PJC2013]

Hi,

I'd be grateful if someone could point out what is going wrong with this formula...I've tried a number of ways including with nested AND with all six dates with IF functions and still no luck. I don't get errors just the wrong result.

Essentially I want it to tell me if a specific date is between one of three ranges 01/01/13 - 31/03/13 = Spring, 01/04/13 - 31/08/13 = Summer, 01/09/13 - 31/12/13 = Winter

Cell D4 = 02/02/13

=IF(D4<=31/3/13,"SPRING",IF(D4<=31/8/13,"SUMMER","WINTER"))
=IF(D4>=1/9/2013,"WINTER",IF(D4>=1/4/13,"SUMMER","SPRING"))

The answer I get is Winter

Many thanks

[TMS]

Instead of 31/3/13, try DATEVALUE("31/03/2013").

And be consistent ... although, if it looks like a valid date, TIMEVALUE should convert it ok


Regards, TMS

[TMS]

Note that 31/3/13 is probably being treated as 31 divided by 3 divided by 13

Regards, TMS

[BadlySpelledBuoy]

OR use DATE(). So your first formula would look like

=IF(D4<=DATE(2013,3,13),"SPRING",IF(D4<=DATE(2013,8,31),"SUMMER","WINTER"))

[FDibbins]

Hi and welcome to the forum

try this...
=IF(D4<=DATE(2013,8,31),"SPRING",IF(D4<=DATE(2013,8,31),"SUMMER","WINTER"))

[TMS]

I also note that your second date is 5 months after the first data ... long season?

Regards, TMS

[PJC2013]

Thanks for the replies and welcome!

adding DATE has worked and it is giving the expected results, however I forgot about blank cells...
So have amended the formula to this:

=IF(AND(D5>=DATE(2013,1,1),D5<=DATE(2013,3,31)),"SPRING",IF(AND(D5>=DATE(2013,4,1),D5<=DATE(2013,8,31)),"SUMMER",IF(AND(D5>=DATE(2013,9,1),D5<=DATE(2013,12,31)),"WINTER","")))

Thanks again for your help!

[TMS]

You're welcome.

[FDibbins]

Happy to help

[popipipo]

For blank cells correction you can use:
=IF(D6="",""....

the new formula becomes:
=IF(D6="","",IF(D6<=DATE(2013,3,31),"SPRING",IF(D6<=DATE(2013,8,31),"SUMMER",IF(D6<=DATE(2013,12,31),"WINTER",""))))

[Teethless mama]

Thanks for the replies and welcome!

adding DATE has worked and it is giving the expected results, however I forgot about blank cells...
So have amended the formula to this:

=IF(AND(D5>=DATE(2013,1,1),D5<=DATE(2013,3,31)),"SPRING",IF(AND(D5>=DATE(2013,4,1),D5<=DATE(2013,8,31)),"SUMMER",IF(AND(D5>=DATE(2013,9,1),D5<=DATE(2013,12,31)),"WINTER","")))

Thanks again for your help!

Try this way... It looks 75% shorter and more elegant than that formula

=IF(D5,LOOKUP(MONTH(D5),{1,4,9},{"Spring","Summer","Winter"}),"")