IF formula not working properly (scheduling/ staffing)

[SHUTTEHFACE]

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

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]Start[/TD]
[TD]End[/TD]
[TD]8[/TD]
[TD]8.5[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]8.5[/TD]
[TD]17[/TD]
[TD][/TD]
[TD][/TD]
[TD][TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]=IF(SUM($C$3:D$3)>=2,0.25,IF(AND(E$2>=$A3,E$2<=$B3),0.5,0))
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

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

 

[SHUTTEHFACE]

bump

 

[adam087]

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]

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

 

[adam087]

It works for me, although I'm not sure what should be in cell E2, F2, etc

Excel 2010

	A	B	C	D	E
Start	End

<colgroup><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]3[/TD]

[TD="align: right"]8[/TD]
[TD="align: right"]8.5[/TD]
[TD="align: right"]9[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]8.5[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]0.25[/TD]
[TD="align: right"]0.25[/TD]
[TD="align: right"]0.25[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C4[/TH]
[TD="align: left"]=IF(SUM($C$3:D$3)>=2,0.25,IF(AND(E$2>=$A3,E$2<=$B3),0.5,0))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



/AJ

 

[SHUTTEHFACE]

Sorry, this is what I am using:

[TABLE="class: cms_table_grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Start[/TD]
[TD]End[/TD]
[TD]8[/TD]
[TD]8.5[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]8.5[/TD]
[TD]17[/TD]
[TD]0[/TD]
[TD]0.5[/TD]
[TD][TABLE="class: cms_table_grid, width: 500, align: center"]
<tbody>[TR]
[TD]=IF(SUM($C$2:D$2)>=2,0.25,IF(AND(E$1>=$A2,E$1<=$B2),0.5,0))
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[SHUTTEHFACE]

bump

 

[SHUTTEHFACE]

bump