Multiple IF Statment

[nods]

Hi I have the a work book where a use would select a start and finish time (hh:mm format) which will then populate a table with 1 and 0's.


the table looks like this but covers the 24 hour clock in 15 minute intervals.

D E F G H I J K
START FINISH 00:00 00:15 00:30 00:45 01:00 01:15
21:00 23:45 0 0 0 0 0 0

The formula i have so far is

Please Login or Register  to view this content.

The table will popluate fine unles the start and finsh time cross midnight.

Would anyone be able to help or point in the right direction to solve this?

Many thanks

nods

Rota.xls


What I want to acheive for example is that if the start time is 22:00 and finish is 01:00 then the table should populate from 22:00 to 23:45 with 1's on the right and from 00:00 to 01:00 with 1's on the left.

[daddylonglegs]

Try like this

=(($D4<=G$3)+($E4>=H$3)+($D4>=$E4)=2)+0