Hi Bob,

Thanks for your quick response. I have another more complicated problem but
it is also a car assignment problem.

The problem is similar to the previous one that assigning right car to the
right job without time overlapping. But there is one more requirement. Once
the car is assigned to DriverA, other drivers cannot use the car until
DriverA drives the car back to the depot.

Using below table,
Column A stores the service type. There are 3 types which are "T", "L" and
"W" services.
Column B and C store the start time snd finish time of the job.
Column D and E are the driver and car assignment for the job. e.g. in row 4,
Driver 3 is assignned for the job. The values in column D are given and I
would like to find out the value of column E.
Column F and column G are the route of the car (from which location to which
location). The value of these two columns are also given. And location 16 is
the depot. So, if the car start at location 16, that means the car go to the
destination from depot.
I have inputted the formulae based on those you taught me before .

A B C D E F G H I
J K L M N O P

----------------------------------------------------------------------------------------------
1) service| job | assignment | location | W truck | L truck
| T truck |
2) type |start| end |driver|vehicle|from| to |W1|W2|W3|L1|L2|L3|T1|T2|T3|

3)---------------------------------------------------------------------------------------------
4) L 12:15 12:40 3 L1 16 32
5) W 12:20 12:40 2 W1 16 17
6) T 12:20 12:35 17 T1 16 28
7) W 12:25 12:45 1 W2 28 61
8) L 12:25 12:40 18 L2 16 70
9) W 12:30 12:50 5 W3 16 66
10) T 12:35 12:50 14 T1 22 17
11) L 12:35 12:50 4 L1 16 62
12) W 12:40 13:00 2 W1 17 41
13) T 12:40 12:55 19 T2 16 61
14) L 12:40 12:55 3 L2 32 46

in H4: =IF(A4="W",C4,0)
in K4: =IF(A4="L",C4,0)
in N4: =IF(A4="T",C4,0)
in H5: =IF(A5="W",IF(MAX(H$4:H4)B5,0,C5),0)
in I5: =IF(OR(SUM($H5:H5)0,$A5="T",$A5="l"),0,IF(MAX(I$4 :I4)$B5,0,$C5))
in J5: =IF(OR(SUM($H5:I5)0,$A5="T",$A5="l"),0,IF(MAX(J$4 :J4)$B5,0,$C5))
in K5: =IF(A5="L",IF(MAX(K$4:K4)$B5,0,$C5),0)
in L5: =IF(OR(SUM($K5:K5)0,$A5="W",$A5="T"),0,IF(MAX(L$4 :L4)$B5,0,$C5))
in M5: =IF(OR(SUM($K5:L5)0,$A5="W",$A5="T"),0,IF(MAX(M$4 :M4)$B5,0,$C5))
in N5: =IF(A5="T",IF(MAX(N$4:N4)B5,0,C5),0)
in O5: =IF(OR(SUM($N5:N5)0,$A5="W",$A5="L"),0,IF(MAX(O$4 :O4)$B5,0,$C5))
in P5: =IF(OR(SUM($N5:O5)0,$A5="W",$A5="L"),0,IF(MAX(P$4 :P4)$B5,0,$C5))

The outputs of column E are shown.

However, you can see that Driver3 (in row 4 and row 14) finished the first
job and go to the location of last job without returning depot. So, he should
use the same car L1. Also, car L1 cannot be used by other drivers in this
period.

So, can you plaese help me to solve this problem? Also, please don't mind my
message is so long and I have so many questions.

Many thanks for your help.

Best regards,
Lilian