I changed the LOOKUP() function in columns D and E to be a VLOOKUP() with the fourth parameter set to 0 so the function looks for an exact match. The LOOKUP() function requires that the lookup vector be ordered. Since column A of the Database sheet is not ordered, incorrect results were returned.

I fixed the formulas in column I to have the form

=H4-G4 + (G4 >H4)

This accounts for cases where the end time (H4) has a smaller clock value (e.g. 07:00) than the start time (e.g. 19:00) but is in the next day. Times are stored in units of days. The time 07:00 has the value 7/24 and the time 19:00 has the value 19/24. You were subtracting

7/24 - 19/24 = -12/24 = -0.5

If a cell is formatted as time, -0.5 is displayed as12:00, but the value is still -0.5.

I appended

+ (G4 > H4)

which returns FALSE (equal to zero) when H4 is larger than or equal to G4. When G4 is larger than H4, it returns TRUE (equal to one) that gives

H4-G4 + (G4 >H4) = 7/24 - 19/24 + (19/24 > 7/24) = -0.5 + (TRUE) = -0.5 + 1 = 0.5

The 0.5, when formatted as time, is 12:00 and it is the desired answer. This is not a bug. All spreadsheets treat times as fractions of a day.

As Peter says, it is better to get help on the forum or the user list.

Francis

---------------------------------------------------------------------

To unsubscribe, e-mail:

[hidden email]
For additional commands, e-mail:

[hidden email]