# Sum formula with mulitple criteria

#### Wimpie

##### Board Regular
Good day

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

### Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
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).

 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 " - "

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"}))),"")

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"))

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)

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

You are welcome.

Replies
1
Views
329
Replies
1
Views
737
Replies
7
Views
568
Replies
16
Views
2K
Replies
1
Views
384

1,196,073
Messages
6,013,267
Members
441,758
Latest member
Abren

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back