Reputation: 623
i have schedule data for assign driver to the user in excel.. here's my data:
All i want to know is how to check time range between other time range (without macro) so i can create a new schedule for the driver if possible..
for example, if some user make request on 24/12/2014 at 11:00 to 12:00, i can assign driver A to do that, because between 11.00 to 12.00, Driver A doesn't have any schedule for delivery.. and if i assign Driver A on 11:00 to 13:00, it will show me not available status because they have delivery schedule on 12:00 to 14:00..
does anyone know how to that? many thanks..
Upvotes: 2
Views: 2494
Reputation: 34440
I have changed the example slightly to limit it to just a named driver. Your drivers don't seem to need any breaks between their jobs so you just want to find any existing slots which overlap with your proposed slot and it looks like this:-
(1) Beginning of existing slot before end of proposed slot
F$9:F$12,"<"&G14
and
(2) End of existing slot after beginning of proposed slot
G$9:G$12,">"&F14
and
(3) Same driver
E$9:E$12,E14
Putting all this together using COUNTIFS and placing this in an IF statement in H14:-
=IF(COUNTIFS(F$9:F$12,"<"&G14,G$9:G$12,">"&F14,E$9:E$12,E14),"Not available","Available")
and pull down to H15.
Upvotes: 2