Display Text IF Statement

[lmmorris]

I'm trying to display which quarter throuout a shift an error was made. I can get Q1 and Q2 to work but for whatever reason Q3 and Q4 returns #Value! as the result.

Formula

=IF(AND([@Time]>=--"07:00:00",[@Time]<=--"09:15:00"),"Q1",IF(AND([@Time]>=--"09:15:00",[@Time]<=--"11:30:00"),"Q2",IF(AND([@Time]>=--"11:30:00",[@Time]<=--"14:45:00","Q3"),IF(AND([@Time]>=--"14:45:00",[@Time]<=--"17:30:00","Q4",),"Night"))))

Time is formatted with custom time h:mm:ss

 

[frank_AL]

I believe your issue is that Excel only supports a maximum of 7 Functions.

See article at: Nested IFs

For info on how to create a Named Formula check out this article: Save effort by defining names for Excel 2007 formulas - TechRepublic

 

[FormR]

I believe your issue is that Excel only supports a maximum of 7 Functions.

Hi, it's the number of nested IF()'s that has the limitation, not the number of functions and the newer versions have a much larger maximum for nested IFS()'s anyway.

It looks like this issue was some some misplaced parentheses.

=IF(AND([@Time]>=--"07:00:00",[@Time]<=--"09:15:00"),"Q1",IF(AND([@Time]>=--"09:15:00",[@Time]<=--"11:30:00"),"Q2",IF(AND([@Time]>=--"11:30:00",[@Time]<=--"14:45:00"),"Q3",IF(AND([@Time]>=--"14:45:00",[@Time]<=--"17:30:00"),"Q4","Night"))))

It's not clear what should happen for times exactly on the boundaries but another alternative might be something like:

=LOOKUP([Time],0+{"00:00","07:00","09:15","11:30","14:45","17:30"},{"Night","Q1","Q2","Q3","Q4","Night"})