Formula not calcuating time worked if shift finishes the next day

[elleb]

Hi,

I have come across a problem with a formula I am using, I have shift times that could finish on or after midnight. I have managed to get the shift length calculating correctly but when I am trying to calculate how many people I have across every 15minute interval it is either not calculating at all or not calculating the last interval (depending on if I put 00:00 or 23:59).

Any help would be appreciated (I have attached an example)

thanks,

[tim201110]

=sum((a$3:a$7<=$f1)*((a$3:a$7+c$3:c$7)>$f1)*$d$3:$d$7)

[PietBom]

I left your formulas, but put a different 00:00 and then it worked ok

[elleb]

Hi,

This works great thanks, the only problem I have is if the time goes over midnight I would need it to calculate into the next day, I have attached an example and what it would look like when counting.

[JeteMc]

Try the following modification to the array entered formula*:

Please Login or Register  to view this content.

*Select cell K2,
Paste the formula into the formula bar,
Simultaneously press the Ctrl, Shift and Enter keys,
Double click the fill handle to copy down.
Let us know if you have any questions.

[elleb]

so sorry for the late reply on this, worked perfectly - thank you.

[JeteMc]

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.