Nested IF formula

[Tomkat]

Hi,
Have been given the following and told to create a formula:

If the office workers work less than 420 hours or the workshop staff work less than 456 hrs, use a formula to display the words "Hours carried over" "Days" (if equal or greater than seven and divide by, "Hours" (if less than seven), or "Nil" depending on the difference in the total hours worked for the 12 week period.

Have tried using =IF(Office<420,"Hours Carried Over",IF(Office>420,Office/7,IF(Office=420,"Nil"))) but think I totally stuffed this up. I have named cell C24 "Office"

Office (Cell C24) Total Hours = 420
Workshop (Cell D24) Total Hours = 462
Workshop (Cell E24) Total Hours = 444

Would greatly appreciate assistance.
Kat

 

[Jonmo1]

Welcome to the board....

It looks like it should work.

My best first guess would be that C24 (Office) is not actually a number, but a number stored as text..

use this to test

=ISNUMBER(Office)

Does that give True or False?

Is C24 manually entered or a result of a formula?
If formula, post that formula please..

 

[Tomkat]

Thanks Jonmo,

Tested = TRUE. C24 was C9 +C16+C22 (monthly subtotal added together to give the total of 420 hours).
Have since tried to remove "Office" as my name range but am unable to delete it.

 

[Jonmo1]

Then the next logical problem would be that c9 c16 c22 are actual TIME values formatted to show the hours.

Time is stored as a decimal number.
1 hour = 1/24 (1 24th of a day) = 0.0416666667
6 hours = 1/4 (1 quarter of a day) = 0.25
12 hours = 1/5 (half a day) 0.5
18 hours = 3/4 (3 quarters of a day) = 0.75
Etc...

So if C24 is 420 hours, that is actually...
420/24 = 17.25 (17 Days and 6 hours)

So in your formula, try converting the 420 to an actual time value of 420 hours

=IF(Office<"420:00:00"+0,"Hours Carried Over",etc...

 

[Jonmo1]

12 hours = 1/5 (half a day) 0.5
that should be
12 hours = 1/2 (half a day) 0.5