Excel: Nested IF statement to check 3 conditions

Question

I have an excel sheet with a column representing time in hours. I need to insert an if statement next to each cell to check the following:

Values less than 24 hours
Values more than 24 hours and less than 48 hours
Values greater than 48 hours

I am able to successfully query for two cases; less than 24 hours and greater than 48 hours. However, my nested IF statement is unable to fetch for the condition where value is more than 24 hours and less than 48 hours. Please help

IF(Q14<=TIME(23,59,59)+TIME(0,0,1), "24", IF(AND(Q14>TIME(23,59,59)+TIME(0,0,1), Q14<TIME(47,59,59)+TIME(0,0,1)),"48", IF(Q14>TIME(48,59,59)+TIME(0,0,1), "96", "Poor ")))

what is the format of the cells in Q14 ? is it a Number or Time (like hh:mm:ss) ? what is the value entered inside this cell (before the conversion) ? — Shai Rado
– Shai Rado, Commented Sep 4, 2016 at 11:58
The value is a calculation of StartTime - EndTime in the format [h]:mm:ss. My values include 44:54:41, 68:08:44 etc. My nested if statement works for "24 hours" and "96 hours" but does not work for "48 hours" — Sarah
– Sarah, Commented Sep 4, 2016 at 12:09

Khiem Tran · Accepted Answer · 2016-09-04 12:20:35Z

2

Use 'HOUR' function to check:

IF(HOUR(Q14) < 24, "under 24", IF(HOUR(Q14) < 48, "24 - 48", "over 48"))

In Excel IF () Function, if you write like this:

IF(HOUR(Q14) < 24, "under 24", ...second case...)

in second case it's true that HOUR(Q14) >= 24, so you don't have to check HOUR(Q14) >= 24 again.

Update

Because in excel you can't input hour > 24. Following your input, If you minus EndTime-StartTime then the real value of the cell is actually number of day. So you can multiply 24 to the value of the cell to get the total hour, following formula:

IF(24 * Q14 < 24, "under 24", IF(24 * Q14 < 48, "under 48", "over 48"))

That's easy. Happy coding!

edited Sep 4, 2016 at 12:20

answered Sep 4, 2016 at 11:44

Khiem Tran

6,16916 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Sarah Over a year ago

I removed the check for the second case modified as

IF(Q13<=TIME(23,59,59)+TIME(0,0,1), "24", IF(Q13<TIME(47,59,59)+TIME(0,0,1),"48", IF(Q13>TIME(48,59,59)+TIME(0,0,1), "96", "Poor ")))

but it still skips the second case

Khiem Tran Over a year ago

Because TIME(47, 59, 59) + TIME(0, 0, 1) returns 10:00 PM. So in excel you can't input time > 24:00:00.

Sarah Over a year ago

okay, so back to my question how do I query based on my question above since i want to query based on the hour values I have as 24, 48 etc

Collectives™ on Stack Overflow

Excel: Nested IF statement to check 3 conditions

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related