Time calculations when going over the midnight

**ABSTRAKTUS** · 06-03-2012, 11:29 AM

Hi all,

I can't get my head round how to make formula kind of "ignore" midnight point. I'm trying to calculate staff availability for the 24 hours period (7:00 am to 7:00 am). Wrote a formula and it works until it reaches midnight. Assume you have col B with No. of heads, col C - time staff available from, col D - time staff available to. In F2 and going across I have a time table starting at 7:00 all the way through to the next 7:00 am.

This is the formula I'm using:

Please Login or Register  to view this content.

The formula returns me 0 if "from - to" is overlaping midnight.
Any ideas gurus?

See attached...

**Richard Buttrey** · 06-03-2012, 11:52 AM

Hi,

How about in F4

=IF(AND($C4<=F$2,$D4>F$2),$B4,IF(ROUND(F$2,6)>=ROUND($C4,6),$B4,0))

copied to all the range in question

**ABSTRAKTUS** · 06-03-2012, 12:00 PM

Hi and thanks for a prompt response. I have applied your formula and faced another issue. Now starting times look alright, but they all end at the end of table. Lets say I have 20 staff available from 23:00 to 4:00, formula is resulting 23:00 start and 7:00 finish.