# IF formula not working properly (scheduling/ staffing)

#### SHUTTEHFACE

##### Board Regular
I am using the following to create a schedule that includes breaks and lunches.

ABCDE
StartEnd88.59
8.517
 =IF(SUM(\$C\$3:D\$3)>=2,0.25,IF(AND(E\$2>=\$A3,E\$2<=\$B3),0.5,0))

<tbody>
</tbody>

<tbody>
</tbody>

The formula is dragged to the right until Row 2 is 24 (24th hour of the day).

The formula keeps returning 0 and I can't figure out why.

Thanks for the help!

M

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

#### SHUTTEHFACE

bump

##### Well-known Member
Excel handles times as real numbers (IE decimals) between 0 and 1; where 0 is midnight and 1 is midnight the next day, making 0.5=Noon.

Therefore if you need to measure for two hours, then instead of using 2, use (2/24).

Hope that helps.

/AJ

#### SHUTTEHFACE

##### Board Regular
Thanks, everything in here is formatted as numbers though so this shouldn't make a different.

##### Well-known Member
It works for me, although I'm not sure what should be in cell E2, F2, etc

Excel 2010
ABCDE
3StartEnd88.59
48.5170.250.250.25

</tbody>
Sheet1

Worksheet Formulas
CellFormula
C4=IF(SUM(\$C\$3:D\$3)>=2,0.25,IF(AND(E\$2>=\$A3,E\$2<=\$B3),0.5,0))

</tbody>

<tbody>
</tbody>

/AJ

#### SHUTTEHFACE

##### Board Regular
Sorry, this is what I am using:

ABCDE
1StartEnd88.59
28.51700.5
 =IF(SUM(\$C\$2:D\$2)>=2,0.25,IF(AND(E\$1>=\$A2,E\$1<=\$B2),0.5,0))

<tbody>
</tbody>

<tbody>
</tbody>

bump

bump

Replies
9
Views
263
Replies
0
Views
275
Replies
1
Views
134
Replies
3
Views
219
Replies
2
Views
311

1,191,584
Messages
5,987,492
Members
440,097
Latest member
Wint

### 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.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

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

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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