I am making a task sheet for my students and I have written the following code:
=IF(J35-K35>5,"Task 1",IF(J35-K35>3,"Task 2",IF(J35-K35>2,"Task 3", IF(J35-K35>0,"Task 4",IF(K35-J35<1,"Task 5",IF(K35-J35<3,"task 6",IF(K35-J35>3,"Task 7")))))))
The cell K35 contains "Total hours worked" and The cell J35 contains "Total hours required". Their format is in [h]:mm
The problem I am facing here is that it is showing me output for only 2 conditions - for the one which is greater than 0 (Task 4) and the other which is less than 1 (task 5). None of the other conditions are getting executed. I need a way in which I can compare hours with numbers. How can I solve this problem?
If the cells contain times, the calculation J35-K35 will return a number between 0 and 1 for a positive value, between -1 and 0 for a negative value. The formula will use the underlying values, not what they will look like if formatted as time.
The value of one hour, formatted as general is 0.041666667. You can either use that and its multiples in your formula or multiply the result of the subtraction by 24 to arrive at numbers where the part before the decimal point is the number of hours, so your formula could be
=IF((J35-K35)*24>5,"Task 1",IF((J35-K35)*24>3,"Task 2",IF((J35-K35)*24>2,"Task 3", IF((J35-K35)*24>0,"Task 4",IF((K35-J35)*24<1,"Task 5",IF((K35-J35)*24<3,"task 6",IF((K35-J35)*24>3,"Task 7")))))))