CODEWITHSUNDEEP
excelexcel-formulaexcel-2010
Michael Frans
Michael Frans

Reputation: 623

How to check time range between multiple time range in excel?

i have schedule data for assign driver to the user in excel.. here's my data:

enter image description here

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

Answers (1)

Tom Sharpe
Tom Sharpe

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.

enter image description here

Upvotes: 2

Related Questions