# Nested IF formula

#### Tomkat

##### New Member
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

### Excel Facts

Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

#### Jonmo1

##### MrExcel MVP
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

##### New Member
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

##### MrExcel MVP
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

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

Replies
3
Views
180
Replies
9
Views
576
Replies
2
Views
353
Replies
1
Views
584
Replies
11
Views
1K

1,190,825
Messages
5,983,106
Members
439,824
Latest member
nellyc

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back