Enter the following in E10
Code:
=(D10>0)*(($D$3-B10)+IF(C10<$D$4,0,C10-$D$4))
and pull down as needed
FWIW in the expression =sum(b5-c5) the sum part is redundant - =b5-c5 does the job
Concerning the #######, that is what XL answers when you get negative time or date difference. That is why you have used correctly =IF(B10>C10,C10+1-B10,C10-B10) which accounts for passing midnight.
You can eventually replace this expression by
You might also want to have a look at absolute and relative references. I suspect you entered all the formula manually without pulling down?
Bookmarks