Sum formula with mulitple criteria

[Wimpie]

Good day

Please assist with the below
I have data as follow
Column A = Date, e.g. 2016/10/01
Column B = Day, e.g. Sunday
Column C = Hours Driven, e.g. 03:53 (Format is [hh]:mm
Column D = Hours worked, e.g. 02:30 (Format is [hh]:mm
Column E = Total Hours worked, e.g. 06:23 (Format is [hh]:mm

Here is what I need in Column F = Overtime hours worked
Column F needs to see what day it is, and the deduct hours based on the day of the week from the total hours worked
From Monday to Friday 07:30 hours need to be worked only then does it become overtime
On Saturdays 04:00 hours need to be removed the rest is overtime
On Sunday no hours must be deducted all the hours is overtime

 

[AliGW]

Please provide a few lines of sample data in tabular format with expected outcomes. You can use the ForumTools add-in for Excel to do this (see the link in my signature line).

 

[Wimpie]

2016/11/01	Tuesday	03:53	04:41	08:34	01:04
2016/11/02	Wednesday	02:48	04:24	07:13	-00:16
2016/11/03	Thursday	01:27	03:10	04:38	-02:52
2016/11/04	Friday	02:22	05:28	07:50	02:20
2016/11/05	Saturday	04:53	04:49	09:42	05:42
2016/11/06	Sunday	01:49	0:41	02:31	02:31

<tbody>
</tbody>

Column G should have a "Text" or "ABS" element as time does not display " - "

 

[Tetra201]

Is this what you need?

=MAX(0,E2-LOOKUP(WEEKDAY(A2,2),{1,6,7},{"7:30","4:00","0:00"}))

If you do not want to see 00:00 for no overtime, then try:

=IFERROR(1/(1/MAX(0,E2-LOOKUP(WEEKDAY(A2,2),{1,6,7},{"7:30","4:00","0:00"}))),"")

 

[Wimpie]

Tetra201, your formula works great.
I do however need to see if there is a negative value
As I explained above it there a way you can incorporate a "Text" and "ABS" into the formula to shoe negative values.
I used the below but it does not help with the determining of the days as $L$1 referred to the 07:30 hours, I would have to use $L$2 for Saturdays and $L$3 for Sundays.
This made the process very manual as the dates and days change
=IF(G6>=$L$1,G6-$L$1,TEXT(ABS(G6-$L$1),"-H:MM"))

 

[Tetra201]

Tetra201, your formula works great.
I do however need to see if there is a negative value...

If you are willing to use a helper column (say, column Z; it may be hidden), then put the following formula in cell Z2 and copy down as needed:

=LOOKUP(WEEKDAY(A2,2),{1,6,7},{"7:30","4:00","0:00"})

Now you can use your IF formula like this: =IF(G2>=Z2,G2-Z2,TEXT(ABS(G2-Z2),"-H:MM"))

However, there is a way to see both positive and negative hours and minutes as numbers, albeit in a slightly unusual format -- for example, 6.23 instead 6:23.

If you want to try this approach , then use the following formula:

=DOLLARFR((E2-LOOKUP(WEEKDAY(A2,2),{1,6,7},{"7:30","4:00","0:00"}))*24,60)

 

[Wimpie]

Thank you Tertra
Both solutions worked great!
Really appreciate the help

 

[Tetra201]

You are welcome.