Calculate Overtime with multiple criteria

bhalbach

Board Regular
Joined
Mar 15, 2018
Messages
221
Office Version
  1. 2016
Platform
  1. Windows
I am trying to make a timesheet that calculates overtime with the following criteria...

1. Overtime is after 8hrs on any given day.
2. Overtime is after 40hrs/week...consideration to the after 8hr criteria to ensure not duplicating overtime.
3. The after 40hrs/week overtime criteria changes if there is a Stat holiday...if there is one stat holiday in the week overtime is calculatd after 32hrs/week,
if there is two stat holiday in the week overtime is calculatd after 24hrs/week etc. Basically for every stat holiday during the week the overtime threshhold changes by 8hrs.

Spreadsheet is set-up with days of the week across the columns, employee names down the rows.
Daily total hours for each employee are entered in the appropriate cells and the formulas need to do all the overtime calculations.
I was planning on using text "Stat" in the cells were a stat holiday occured.

I have managed to calculate all the criteria except the "Stat" holiday criteria without circular references but I am now stumped and have not found a solution on any posts.
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Please ensure that your explanation of how overtime is calculated is complete.

N.B. The Mon - Sun are actual dates formatted to just show day of the week.


Excel 2010
CDEFGHIJK
2MonTueWedThuFriSatSunTotal
3Hours557131381061
4Regular32
5OT @ 1.529
1aaa
Cell Formulas
RangeFormula
K3=SUM(D3:J3)
K4=MIN(40-SUMPRODUCT(--(rHolidays=D2:J2))*8,SUM(D3:J3)-SUMPRODUCT(--(D3:J3>8),D3:J3-8))
K5=K3-K4
Named Ranges
NameRefers ToCells
rHolidays='1aaa'!$M$2:$M$25
 
Upvote 0
The example below includes another alternative. "Stat" is shown above the days of the week.


Excel 2010
DEFGHIJKLM
1StatOvertime
2MonTueWedThuFriSatSunTotalRegOvertime
35571313810613229
41010101040328
1aaa
Cell Formulas
RangeFormula
K3=SUM(D3:J3)
K4=SUM(D4:J4)
L3=MIN(40-SUMPRODUCT(--(rHolidays=D2:J2))*8,SUM(D3:J3)-SUMPRODUCT(--(D3:J3>8),D3:J3-8))
L4=MIN(40-COUNTIF(D1:J1,"Stat")*8,SUM(D4:J4)-SUMPRODUCT(--(D4:J4>8),D4:J4-8))
M3=K3-L3
M4=K4-L4
Named Ranges
NameRefers ToCells
rHolidays='1aaa'!$O$2:$O$22
 
Upvote 0
What is the rHolidays? In cells O2:O22

I am an amateur at this so I may have some questions...
 
Upvote 0
What is the rHolidays?
The dates of the holidays are in a range of cells.
I named this range rHolidays.

The first line of the example counts the number of days in the week that are included in the range named rHolidays.

The second line of the example does not use the holiday dates; it just counts the number of columns with the heading "stat".
 
Upvote 0
I had a quick look and it seems to be working...I will test it further and let you know

Thanks for assist...greatly appreciated
 
Upvote 0
Works great thanks so much.

What would I need to do to display "Stat" above the date that it occurs. I am trying to utilize you "rHolidays" formula but would also like a visable indicator above the date that the stat occurs.

Also, I am going to utilize this for a biweekly timesheet...I would like to have some totals for each week (Week 1 & Week 2) populate a summary sheet, each week would be a new row. Essentially I would have 26 worksheets, with 2 weeks on each sheet that I want to populate to a summary sheet. One problem I see is the totals row can change location on the worksheet dependent on how may employees are in the timesheet for each week.
 
Upvote 0
I illustrate two possibilities for putting "Stat" above the date; you can use either one.
Copy the formula across all the relevant columns.

How you recap the time sheet depends in part on how the data is on the spreadsheet.


Excel 2010
CDEFGHIJKLM
1WeekStatStat
2MonTueWedThuFriSatSunTotalRegOvertime
35571313810612437
4
5
6T201901a1aaa
1aaa
Cell Formulas
RangeFormula
D1=IF(COUNTIF(rHolidays,D2),"Stat","")
E1=IF(MATCH(E2,rHolidays,0),"Stat","")
K3=SUM(D3:J3)
L3=MIN(40-SUMPRODUCT(--(rHolidays=D2:J2))*8,SUM(D3:J3)-SUMPRODUCT(--(D3:J3>8),D3:J3-8))
M3=K3-L3
Named Ranges
NameRefers ToCells
rHolidays='1aaa'!$O$2:$O$22
 
Last edited:
Upvote 0
That worked...

I was thinking of a setup as below..

[TABLE="width: 1984"]
<colgroup><col><col><col><col span="7"><col span="3"><col><col span="2"><col span="3"><col><col><col span="2"><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Stat[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1.5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5.77%[/TD]
[TD="align: right"]5.10%[/TD]
[TD="align: right"]1.62%[/TD]
[TD][/TD]
[TD="align: right"]2.27%[/TD]
[TD="align: right"]4.00%[/TD]
[TD="align: right"]2.60%[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Name[/TD]
[TD]Rate[/TD]
[TD]17-Feb-19[/TD]
[TD]18-Feb-19[/TD]
[TD]19-Feb-19[/TD]
[TD]20-Feb-19[/TD]
[TD]21-Feb-19[/TD]
[TD]22-Feb-19[/TD]
[TD]23-Feb-19[/TD]
[TD]Total[/TD]
[TD]Reg[/TD]
[TD]Overtime[/TD]
[TD]OT %[/TD]
[TD]Regular[/TD]
[TD]Overtime[/TD]
[TD]Vacation[/TD]
[TD]CPP[/TD]
[TD]EI Employee[/TD]
[TD]Employee Net[/TD]
[TD]EI Employer[/TD]
[TD]Stat[/TD]
[TD]WCB[/TD]
[TD]Gross Cost[/TD]
[TD]Cost/hr[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Sun[/TD]
[TD]Mon[/TD]
[TD]Tue[/TD]
[TD]Wed[/TD]
[TD]Thu[/TD]
[TD]Fri[/TD]
[TD]Sat[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Joe[/TD]
[TD] $ 10.00[/TD]
[TD] 1.00[/TD]
[TD] [/TD]
[TD] 16.00[/TD]
[TD] 16.00[/TD]
[TD] 16.00[/TD]
[TD] 8.00[/TD]
[TD] [/TD]
[TD] 57.00[/TD]
[TD] 32.00 [/TD]
[TD] 25.00 [/TD]
[TD="align: right"]43.86%[/TD]
[TD] $ 320.00[/TD]
[TD] $ 375.00[/TD]
[TD] $ 40.10[/TD]
[TD] $ 35.45[/TD]
[TD] $ 11.26[/TD]
[TD] $ 648.30[/TD]
[TD] $ 15.76[/TD]
[TD] $ 12.80[/TD]
[TD] $ 18.07[/TD]
[TD] $ 817.18[/TD]
[TD] $ 14.34[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Fred[/TD]
[TD] $ 15.00[/TD]
[TD] 1.00[/TD]
[TD] 8.00[/TD]
[TD] 16.00[/TD]
[TD] 16.00[/TD]
[TD] 16.00[/TD]
[TD] 16.00[/TD]
[TD] 16.00[/TD]
[TD] 89.00[/TD]
[TD] 32.00 [/TD]
[TD] 57.00[/TD]
[TD="align: right"]64.04%[/TD]
[TD] $ 480.00[/TD]
[TD] $ 1,282.50[/TD]
[TD] $ 101.70[/TD]
[TD] $ 89.89[/TD]
[TD] $ 28.55[/TD]
[TD] $ 1,644.06[/TD]
[TD] $ 39.97[/TD]
[TD] $ 19.20[/TD]
[TD] $ 45.83[/TD]
[TD] $ 2,059.08[/TD]
[TD] $ 23.14[/TD]
[/TR]
[TR]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD="align: right"]0.00%[/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[/TR]
[TR]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD="align: right"]0.00%[/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[/TR]
[TR]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD="align: right"]0.00%[/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[/TR]
[TR]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD="align: right"]0.00%[/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[/TR]
[TR]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD="align: right"]0.00%[/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Totals Week 1[/TD]
[TD][/TD]
[TD] 2.00[/TD]
[TD] 8.00[/TD]
[TD] 32.00[/TD]
[TD] 32.00[/TD]
[TD] 32.00[/TD]
[TD] 24.00[/TD]
[TD] 16.00[/TD]
[TD] 146.00[/TD]
[TD] 64.00[/TD]
[TD] 82.00[/TD]
[TD="align: right"]56.16%[/TD]
[TD] $ 800.00[/TD]
[TD] $ 1,657.50[/TD]
[TD] $ 141.80[/TD]
[TD] $ 125.33[/TD]
[TD] $ 39.81[/TD]
[TD] $ 2,292.36[/TD]
[TD] $ 55.74[/TD]
[TD] $ 32.00[/TD]
[TD] $ 63.90[/TD]
[TD] $ 2,876.26[/TD]
[TD] $ 19.70[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1.5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5.77%[/TD]
[TD="align: right"]5.10%[/TD]
[TD="align: right"]1.62%[/TD]
[TD][/TD]
[TD="align: right"]2.27%[/TD]
[TD="align: right"]4.00%[/TD]
[TD="align: right"]2.60%[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Name[/TD]
[TD]Rate[/TD]
[TD]24-Feb-19[/TD]
[TD]25-Feb-19[/TD]
[TD]26-Feb-19[/TD]
[TD]27-Feb-19[/TD]
[TD]28-Feb-19[/TD]
[TD]1-Mar-19[/TD]
[TD]2-Mar-19[/TD]
[TD]Total[/TD]
[TD]Reg[/TD]
[TD]Overtime[/TD]
[TD]OT %[/TD]
[TD]Regular[/TD]
[TD]Overtime[/TD]
[TD]Vacation[/TD]
[TD]CPP[/TD]
[TD]EI Employee[/TD]
[TD]Employee Net[/TD]
[TD]EI Employer[/TD]
[TD]Stat[/TD]
[TD]WCB[/TD]
[TD]Gross Cost[/TD]
[TD]Cost/hr[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Sun[/TD]
[TD]Mon[/TD]
[TD]Tue[/TD]
[TD]Wed[/TD]
[TD]Thu[/TD]
[TD]Fri[/TD]
[TD]Sat[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Joe[/TD]
[TD] $ 10.00[/TD]
[TD] - [/TD]
[TD] 8.00[/TD]
[TD] 8.00[/TD]
[TD] 8.00[/TD]
[TD] 8.00[/TD]
[TD] 16.00[/TD]
[TD] - [/TD]
[TD] 48.00[/TD]
[TD] 32.00 [/TD]
[TD] 16.00 [/TD]
[TD="align: right"]33.33%[/TD]
[TD] $ 320.00[/TD]
[TD] $ 240.00[/TD]
[TD] $ 32.31[/TD]
[TD] $ 28.56[/TD]
[TD] $ 9.07[/TD]
[TD] $ 522.37[/TD]
[TD] $ 12.70[/TD]
[TD] $ 12.80[/TD]
[TD] $ 14.56[/TD]
[TD] $ 660.93[/TD]
[TD] $ 13.77[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Fred[/TD]
[TD] $ 15.00[/TD]
[TD] - [/TD]
[TD] 8.00[/TD]
[TD] 8.00[/TD]
[TD] 8.00[/TD]
[TD] 8.00[/TD]
[TD] 8.00[/TD]
[TD] 8.00[/TD]
[TD] 48.00[/TD]
[TD] 32.00 [/TD]
[TD] 16.00[/TD]
[TD="align: right"]33.33%[/TD]
[TD] $ 480.00[/TD]
[TD] $ 360.00[/TD]
[TD] $ 48.47[/TD]
[TD] $ 42.84[/TD]
[TD] $ 13.61[/TD]
[TD] $ 783.55[/TD]
[TD] $ 19.05[/TD]
[TD] $ 19.20[/TD]
[TD] $ 21.84[/TD]
[TD] $ 991.40[/TD]
[TD] $ 20.65[/TD]
[/TR]
[TR]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD="align: right"]0.00%[/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[/TR]
[TR]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD="align: right"]0.00%[/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[/TR]
[TR]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD="align: right"]0.00%[/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[/TR]
[TR]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD="align: right"]0.00%[/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[/TR]
[TR]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD="align: right"]0.00%[/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Totals Week 2[/TD]
[TD][/TD]
[TD] - [/TD]
[TD] 16.00[/TD]
[TD] 16.00[/TD]
[TD] 16.00[/TD]
[TD] 16.00[/TD]
[TD] 24.00[/TD]
[TD] 8.00[/TD]
[TD] 96.00[/TD]
[TD] 64.00[/TD]
[TD] 32.00[/TD]
[TD="align: right"]33.33%[/TD]
[TD] $ 800.00[/TD]
[TD] $ 600.00[/TD]
[TD] $ 80.78[/TD]
[TD] $ 71.40[/TD]
[TD] $ 22.68[/TD]
[TD] $ 1,305.92[/TD]
[TD] $ 31.75[/TD]
[TD] $ 32.00[/TD]
[TD] $ 36.40[/TD]
[TD] $ 1,652.33[/TD]
[TD] $ 17.21[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0

Excel 2010
ABCDEFGHIJKLMNO
2Week Stat
3Name17-Feb-19SunMonTueWedThuFriSatTotalRegOvertimeRateTotal
4Joe17-Feb-1911616168573225$10.00$695.00
5Fred17-Feb-19181616161616893257$15.00$1,762.50
6
724-Feb-19SunMonTueWedThuFriSat
8Joe24-Feb-1911616168573324$10.00$690.00
9Fred24-Feb-19181616161616894049$15.00$1,702.50
Time
Cell Formulas
RangeFormula
D2=IF(COUNTIF(rHolidays,D3),"Stat","")
D3=$C3+(COLUMN()-4)
E2=IF(MATCH(E3,rHolidays,0),"Stat","")
K5=SUM(D5:J5)
K4=SUM(D4:J4)
K8=SUM(D8:J8)
K9=SUM(D9:J9)
L4=MIN(40-SUMPRODUCT(--(rHolidays=D$3:J$3))*8,SUM(D4:J4)-SUMPRODUCT(--(D4:J4>8),D4:J4-8))
L8=MIN(40-SUMPRODUCT(--(rHolidays=D$7:J$7))*8,SUM(D8:J8)-SUMPRODUCT(--(D8:J8>8),D8:J8-8))
M4=K4-L4
M5=K5-L5
O4=L4*N4+M4*1.5*N4
O5=L5*N5+M5*1.5*N5
Named Ranges
NameRefers ToCells
rHolidays=Time!$Q$3:$Q$25



Excel 2010
ABCDE
1Week of
2NameStartEndAmount
3Joe17-Feb-1924-Feb-19$1,385.00
4Fred17-Feb-1924-Feb-19$3,465.00
5
Summary_Time
Cell Formulas
RangeFormula
E3=SUMIFS(Time!O:O,Time!A:A,A3,Time!C:C,">="&C3,Time!C:C,"<="&D3)
 
Upvote 0

Forum statistics

Threads
1,223,157
Messages
6,170,420
Members
452,325
Latest member
BlahQz

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top