Problem with Nested IF function

[icthus123]

I'm working a spreadsheet to pick up when a given day is a public holiday, but for some reason when I add the last IF function in the formula below it starts showing an error. This is odd because the function works fine on its own. This last section is designed to catch when New Years Day falls on a weekend.

=IF(A2=FLOOR(DAY(MINUTE(YEAR(A2)/38)/2+56)&"/5/"&YEAR(A2),7)-36,"GOOD FRIDAY",IF(A2=FLOOR(DAY(MINUTE(YEAR(A2)/38)/2+56)&"/5/"&YEAR(A2),7)-35,"EASTER SATURDAY",IF(A2=FLOOR(DAY(MINUTE(YEAR(A2)/38)/2+56)&"/5/"&YEAR(A2),7)-34,"EASTER SUNDAY",IF(A2=FLOOR(DAY(MINUTE(YEAR(A2)/38)/2+56)&"/5/"&YEAR(A2),7)-33,"EASTER MONDAY",IF(AND(DAY(A2)=1,MONTH(A2)=1),"NEW YEARS DAY",IF(AND(OR(DAY(A2)=2,DAY(A2)=3),MONTH(A2)=1,WEEKDAY((DATE(YEAR(A2),1,1) ),2)>5,WEEKDAY(A2)=2),"HOLIDAY IN LIEU",""))))))

Any comments as to the problem would be appreciated.

Edited 4-Sep-2009 by geekgirlau. Reason: Remove code tag to force text to wrap

[Benzadeus]

Probably you are having this problem because you nested more then 7 IF functions.

To fix this issue, see http://www.cpearson.com/excel/nested.htm

[icthus123]

There aren't more than 7 IF's nested.

[mdmackillop]

You code works for me in 2007, but did not earlier using 2003. Can't see why this would be.

[rbrhodes]

Slight variation:

=IF(A2=FLOOR(DAY(MINUTE(YEAR(A2)/38)/2+56)&"/5/"&YEAR(A2),7)-36,"GOOD FRIDAY",IF(A2=FLOOR(DAY(MINUTE(YEAR(A2)/38)/2+56)&"/5/"&YEAR(A2),7)-35,"EASTER SATURDAY",IF(A2=FLOOR(DAY(MINUTE(YEAR(A2)/38)/2+56)&"/5/"&YEAR(A2),7)-34,"EASTER SUNDAY",IF(A2=FLOOR(DAY(MINUTE(YEAR(A2)/38)/2+56)&"/5/"&YEAR(A2),7)-33,"EASTER MONDAY",IF(AND(DAY(A2)=1,MONTH(A2)=1),IF(WEEKDAY(A2,2)>5,"HOLIDAY IN LIEU","NEW YEARS DAY"),"")))))

[icthus123]

Right, it seems to accept that. Thanks. Do you know why it didn't accept the previous one? There doesn't seem to be anything wrong with it as far as I can see, and apparently it works in office 2007.

[rbrhodes]

I tried in 03 and 07 and had the same results. It is something to do with too many IF's - although there isn't even the limit. Haven't figured out why tho...

[p45cal]

I'm working a spreadsheet to pick up when a given day is a public holiday, but for some reason when I add the last IF function in the formula below it starts showing an error. This is odd because the function works fine on its own. This last section is designed to catch when New Years Day falls on a weekend.

=IF(A2=FLOOR(DAY(MINUTE(YEAR(A2)/38)/2+56)&"/5/"&YEAR(A2),7)-36,"GOOD FRIDAY",IF(A2=FLOOR(DAY(MINUTE(YEAR(A2)/38)/2+56)&"/5/"&YEAR(A2),7)-35,"EASTER SATURDAY",IF(A2=FLOOR(DAY(MINUTE(YEAR(A2)/38)/2+56)&"/5/"&YEAR(A2),7)-34,"EASTER SUNDAY",IF(A2=FLOOR(DAY(MINUTE(YEAR(A2)/38)/2+56)&"/5/"&YEAR(A2),7)-33,"EASTER MONDAY",IF(AND(DAY(A2)=1,MONTH(A2)=1),"NEW YEARS DAY",IF(AND(OR(DAY(A2)=2,DAY(A2)=3),MONTH(A2)=1,WEEKDAY((DATE(YEAR(A2),1,1) ),2)>5,WEEKDAY(A2)=2),"HOLIDAY IN LIEU",""))))))

Any comments as to the problem would be appreciated.

Edited 4-Sep-2009 by geekgirlau. Reason: Remove code tag to force text to wrap

I had the same problem when trying this out in 2003. Split up, everything worked, but as an all-in-one mega-formula an error showed.

Since this only caters for Easter and New Year periods I'd imagine you will want to extend this formula to include Christmas, other bank holidays and so on. This is going to get messy from 2 standpoints; (a) a difficult-to-understand formula therefore difficult to tweak and (b) a longer formula which will error because it's too long or limits to If nesting and the like are breached.

I feel this sort of thing is crying out for a user defined function. Here's one based on your logic that you'd use in the worksheet thus:

=blah(A2)

backed up by the function in a standard code module:[vba]Function blah(z)
x = CLng(Application.WorksheetFunction.Floor(DateSerial(Year(z), 5, Day(Minute(Year(z) / 38) / 2 + 56)), 7) - z)
Select Case True
Case x = 36: blah = "Good Friday"
Case x = 35: blah = "Easter Saturday"
Case x = 34: blah = "Easter Sunday"
Case x = 33: blah = "Easter Monday"
Case Month(z) = 1 And Day(z) = 1: blah = "New Year's Day"
Case (Day(z) = 2 Or Day(z) = 3) And Month(z) = 1 And Weekday(DateSerial(Year(z), 1, 1), vbMonday) > 5 And Weekday(z) = 2: blah = "Holiday in lieu"
'Case Day(z) = 25 And Month(z) = 12: blah = "Christmas Day"
'Case Day(z) = 4 And Month(z) = 7: blah = "Independence Day"
Case Else
blah = "none of the above"
End Select
End Function
[/vba](Substitute blah throughout for a more suitable name to the function)
I've put in a couple of commented-out lines to show how it can be tweaked.

[Mikey]

Slight variation:

=IF(A2=FLOOR(DAY(MINUTE(YEAR(A2)/38)/2+56)&"/5/"&YEAR(A2),7)-36,"GOOD FRIDAY",IF(A2=FLOOR(DAY(MINUTE(YEAR(A2)/38)/2+56)&"/5/"&YEAR(A2),7)-35,"EASTER SATURDAY",IF(A2=FLOOR(DAY(MINUTE(YEAR(A2)/38)/2+56)&"/5/"&YEAR(A2),7)-34,"EASTER SUNDAY",IF(A2=FLOOR(DAY(MINUTE(YEAR(A2)/38)/2+56)&"/5/"&YEAR(A2),7)-33,"EASTER MONDAY",IF(AND(DAY(A2)=1,MONTH(A2)=1),IF(WEEKDAY(A2,2)>5,"HOLIDAY IN LIEU","NEW YEARS DAY"),"")))))

I don't think this copes correctly with New Year. The intention, in the original, is to show 1st Jan as New Years Day but also to show the 2nd or 3rd Jan as "Holiday in Lieu" if that day is a Monday and 1st jan is a Sat or Sun.

The reason that the original doesn't work is that you have more than 7 nested functions (the limit isn't restricted to IFs, it includes any functions, so the accumulation of IFs, ANDs Ors etc. breaches that)

Try this version

=IF(MONTH(A2)=1,IF(DAY(A2)=1,"NEW YEARS DAY",IF(AND(OR(DAY(A2)={2,3}),WEEKDAY(A2)=2),"HOLIDAY IN LIEU","")),IF(ISNA(MATCH(A2,FLOOR(DAY(MINUTE(YEAR(A2)/38)/2+56)&"/5/"&YEAR(A2),7)-{36,35,34,33},0)),"",LOOKUP(A2,FLOOR(DAY(MINUTE(YEAR(A2)/38)/2+56)&"/5/"&YEAR(A2),7)-{36,35,34,33},{"GOOD FRIDAY","EASTER SATURDAY","EASTER SUNDAY","EASTER MONDAY"})))

[Paul_Hossler]

Possibly a dumb question, but why do you use FLOOR() and MINUTE() in that really long worksheet formula?

It would seem to me (at first blush) that you could just pick apart the input date by using MONTH() and DAY()

Paul

[Mikey]

Possibly a dumb question, but why do you use FLOOR() and MINUTE() in that really long worksheet formula?

Hello Paul,

It's about the simplest way to calculate the date of Easter (by formula), given the year, one of the formulas suggested here

[Paul_Hossler]

Told you it was a dumb question

Not only do I have Chip's very excellent site bookmarked, to add dumbness to dumbness, I had earlier included his code in my module with all my date-releated functions

Paul