IF formula not working properly (scheduling/ staffing)

[SHUTTEHFACE]

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

A	B	C	D	E
Start	End	8	8.5	9
8.5	17			=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

 

[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
3	Start	End	8	8.5	9
4	8.5	17	0.25	0.25	0.25

<colgroup><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula C4 =IF(SUM($C$3:D$3)>=2,0.25,IF(AND(E$2>=$A3,E$2<=$B3),0.5,0)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

/AJ

 

[SHUTTEHFACE]

Sorry, this is what I am using:

	A	B	C	D	E
1	Start	End	8	8.5	9
2	8.5	17	0	0.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>

 

[SHUTTEHFACE]

bump

 

[SHUTTEHFACE]

bump