Formula not calculating all variables correctly

[Ochimus]

Following formula should calculate how many shift hours are "premium":

=IF(AND(OR(ISNUMBER(SEARCH("AM*",A17)),ISNUMBER(SEARCH("DOUBLE*",A17)),B17 < TIME(8,0,0))),TIME(8,45,0)-B17)+IF(AND(ISNUMBER(SEARCH("AM*",A17)),C17>TIME(14,15,0)),C17-TIME(14,15,0))+IF(AND(ISNUMBER(SEARCH("PM*",A17)),B17 < TIME(12,45,0)),C17-TIME(12,45,0))+IF(AND(OR(ISNUMBER(SEARCH("DOUBLE*",A17)),ISNUMBER(SEARCH("PM*",A17)),C17 > TIME(17,30,0))),C17-TIME(17,30,0))

Should work as follows:

1. AM or Double Shift starts
If employee starts before 08:00, Start time until 08:45 is "Premium"
If employee starts on or after 08:00, then no Premium hours

2.AM shift ends
If employee finishes later than 14:15, then 14:15 to Finish time are "Premium"

3. PM Shift Starts
If employee starts before 12:45, Start time until 12:45 is "Premium"

3. PM or Double Shifts end
If employee finishes later than 17:30, then 17:30 to Finish time are "Premium"

Unfortunately, as shown on the attachment, some combinations are generating wrong answers:

AM Shift 08:00 - 14:15 = 00:45 S/be no premium - started at 08:00
AM Shift 09:00 - 15:00 = 00:30 S/be Premium 14:15 - 15:00

Double 07:00 - 16:00 = 00:15 S/be Premium 07:00 - 08:45
Double 08:00 - 17:30 = 00:45 S/be no premium - started at 08:00, finished 17:30
Double 09:00 - 19:00 = 01:15 S/be Premium 17:30 - 19:00

Hopefully someone can see the flaw? All suggestions, pointers and solutions accepted grqatefully.

Ochimus

[JohnTopley]

Try

=IF(AND(OR(ISNUMBER(SEARCH("AM*",A16)),ISNUMBER(SEARCH("DOUBLE*",A16))),B16 < TIME(8,0,0)),TIME(8,45,0)-B16,0)+IF(AND(ISNUMBER(SEARCH("AM*",A16)),C16>TIME(14,15,0)),C16-TIME(14,15,0),0)+IF(AND(ISNUMBER(SEARCH("PM*",A16)),B16 < TIME(12,45,0)),TIME(12,45,0)-B16)+IF(AND(OR(ISNUMBER(SEARCH("DOUBLE*",A16)),ISNUMBER(SEARCH("PM*",A16))),C16 > TIME(17,30,0)),C16-TIME(17,30,0),0)

[Ochimus]

John,

Many thanks for prompt response, which works perfectly,

I'll leave it until the morning to compare them and see where I missed something

Ochimus

[JohnTopley]

Differences ....(your formula below)

Extra brackets and wrong calculation ..

=IF(AND(OR(ISNUMBER(SEARCH("AM*",A17)),ISNUMBER(SEARCH("DOUBLE*",A17)),B17 < TIME(8,0,0))),TIME(8,45,0)-B17)+IF(AND(ISNUMBER(SEARCH("AM*",A17)),C17>TIME(14,15,0)),C17-TIME(14,15,0))+IF(AND(ISNUMBER(SEARCH("PM*",A17)),B17 < TIME(12,45,0)),C17-TIME(12,45,0))+IF(AND(OR(ISNUMBER(SEARCH("DOUBLE*",A17)),ISNUMBER(SEARCH("PM*",A17)),C17 > TIME(17,30,0))),C17-TIME(17,30,0))