Needed Formulas for time card and Total earned

[serge]

I need to calculate a time card.

For regular time,
over time by x 1.5
over time by x 2
and have at the end, how much is been earned in a total $ amount.
The hour break down goes by, 1/4 - 1/2 - 3/4 - hour.

I fill up manually what should show in the yellow cell.

What are the formulas that would give me those results ?

See the Table below.

Thank you.

Excel Workbook
	A	B	C	D	E	F	G
1	Week Day	Hourly Rate	Total Hours Worked	Straight	Over Time X 1.5	Over Time X 2	Total $ amount per day
2		$11.50		8 Hours	from 8 to 12	After 12
3
4	Monday		15.45	8	4	3.45	$247.25
5	Tuesday		9	8	1	0	$109.25
6	Wednesday		18.15	8	4	6.15	$304.75
7	Thursday		7	7	0	0	$80.50
8	Friday
9	Saturday
10	Sunday
11
12					Total for the week		$741.75
Sheet1

 

[sulakvea]

in G4, i typed =D4*11.5+E4*11.5*1.5+F4*11.5*2 (according to the payrate) - and i got 240.35, not 247.25. how did you get that 247 number?

 

[Akashwani]

Hi Serge,

I don't get the figures you do, I've probably made a calculation error somewhere, but this will get you on your way....

Excel Workbook
	A	B	C	D	E	F	G	H
1	Week Day	Hourly Rate	Total Hours Worked	Straight Hours	Over Time X 1.5	Over Time X 2	Total $ amount per day
2		$11.50		8	12	After 12
3
4	Monday		15.45	8	4	3.45	$240.35	$247.25
5	Tuesday		9	8	1	0	$109.25	$109.25
6	Wednesday		18.15	8	4	6.15	$302.45	$304.75
7	Thursday		7	7	0	0	$80.50	$80.50
8	Friday
9	Saturday
10	Sunday
11
12					Total for the week	732.55
Sheet2

Good luck.

Ak

 

[meldoc]

Hello

The total hours worked per day, have to be entered as a decimal IE .25 for a 1/4 hour Etc.

Excel Workbook
	A	B	C	D	E	F	G
1	Week Day	Hourly Rate	Total Hours Worked	Straight	Over Time X 1.5	Over Time X 2	Total $ amount per day
2		11.5		8 Hours	from 8 to 12	After 12
3
4	Monday		15.75	8	4	3.75	247.25
5	Tuesday		9	8	1	0	109.25
6	Wednesday		18.25	8	4	6.25	304.75
7	Thursday		7	7	0	0	80.5
8	Friday
9	Saturday
10	Sunday
11
12						Total for the week	741.75
Sheet1

Excel 2010

Cell Formulas
Range	Formula
D4	=MIN(C4,8)
E4	=MIN((C4-D4),4)
F4	=C4-SUM(D4:E4)
G4	=(D4*B$2)+(E4*B$2*1.5)+(F4*B$2*2)
G12	=SUM(G4:G10)

 

[jasonb75]

Maybe

Excel Workbook
	A	B	C	D	E	F	G
1	Week Day	Hourly Rate	Total Hours Worked	Straight	Over Time X 1.5	Over Time X 2	Total $ amount per day
2		$11.50		8 Hours	from 8 to 12	After 12
3
4	Monday		15:45	08:00	04:00	03:45	$247.25
5	Tuesday		09:00	08:00	01:00	00:00	$109.25
6	Wednesday		18:15	08:00	04:00	06:15	$304.75
7	Thursday		07:00	07:00	00:00	00:00	$80.50
8	Friday
9	Saturday
10	Sunday
11
12					Total for the week		$741.75
Sheet

 

[serge]

I really appreciate your prompt responses and all the answers you gave me.

Thank you all.

Best Regards...
Serge.

 

[serge]

jasonb75,

I forgot one question regarding the time and fee.

If I need to open a new tread let me know, but I'm gone ask the question here.

Keeping the same set up than in your post # 5.

What formulas should be used if the rate is $ 500. for 10 hours.

and x 1.5 from 10 to 12.

and x 2 after 12.

So the different here is that the first 10 hours have a fix of $ 500 and not per hour !! and the x 1.5 and x 2 are also based on the $ 500.

So to be clear the x 1.5 after would make 1 hour at : $ 75.00

and the x 2 at : $ 100.

Thank you in advance.
Serge.

 

[jasonb75]

Using the same layout.

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 83px"><COL style="WIDTH: 104px"><COL style="WIDTH: 154px"><COL style="WIDTH: 74px"><COL style="WIDTH: 111px"><COL style="WIDTH: 99px"><COL style="WIDTH: 181px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc; FONT-FAMILY: Verdana; FONT-SIZE: 10pt; FONT-WEIGHT: bold" rowSpan=2>Week Day</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc; FONT-FAMILY: Verdana; FONT-SIZE: 10pt; FONT-WEIGHT: bold">10 Hour Rate</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc; FONT-FAMILY: Verdana; FONT-SIZE: 10pt; FONT-WEIGHT: bold" rowSpan=2>Total Hours Worked</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc; FONT-FAMILY: Verdana; FONT-SIZE: 10pt; FONT-WEIGHT: bold">Straight</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc; FONT-FAMILY: Verdana; FONT-SIZE: 8pt; FONT-WEIGHT: bold">Over Time X 1.5</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc; FONT-FAMILY: Verdana; FONT-SIZE: 8pt; FONT-WEIGHT: bold">Over Time X 2</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc; FONT-FAMILY: Verdana; FONT-SIZE: 10pt; FONT-WEIGHT: bold" rowSpan=2>Total $ amount per day</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: Verdana; FONT-SIZE: 12pt; FONT-WEIGHT: bold">$500.00</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc; FONT-FAMILY: Verdana; FONT-SIZE: 10pt; FONT-WEIGHT: bold">10 Hours</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc; FONT-FAMILY: Verdana; FONT-SIZE: 9pt; FONT-WEIGHT: bold">from 10 to 12</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc; FONT-FAMILY: Verdana; FONT-SIZE: 9pt; FONT-WEIGHT: bold">After 12</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="BACKGROUND-COLOR: #ccffcc; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Monday</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">15:45</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">10:00</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">02:00</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">03:45</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">$1,025.00</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>D4</TD><TD>=MEDIAN(0,--"10:00",C4)</TD></TR><TR><TD>E4</TD><TD>=MEDIAN(0,C4-D4,--"2:00")</TD></TR><TR><TD>F4</TD><TD>=MEDIAN(0,C4-D4-E4,--"12:00")</TD></TR><TR><TD>G4</TD><TD>=MAX($B$2,SUMPRODUCT((D4:F4*24)*({1,1.5,2})*($B$2/10)))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

If you're going to have constantly changing rates / hour caps maybe it would be worth considering some sheet layuot changes so that you don't have to keep re-writing the formula each time.

As a quick example,

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 83px"><COL style="WIDTH: 104px"><COL style="WIDTH: 154px"><COL style="WIDTH: 74px"><COL style="WIDTH: 111px"><COL style="WIDTH: 99px"><COL style="WIDTH: 181px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc; FONT-FAMILY: Verdana; FONT-SIZE: 10pt; FONT-WEIGHT: bold">Rate</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc; FONT-FAMILY: Verdana; FONT-SIZE: 10pt; FONT-WEIGHT: bold">Per (Hours)</TD><TD> </TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc; FONT-FAMILY: Verdana; FONT-SIZE: 10pt; FONT-WEIGHT: bold">Straight</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc; FONT-FAMILY: Verdana; FONT-SIZE: 8pt; FONT-WEIGHT: bold">Over Time X 1.5</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc; FONT-FAMILY: Verdana; FONT-SIZE: 8pt; FONT-WEIGHT: bold">Over Time X 2</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc; FONT-FAMILY: Verdana; FONT-SIZE: 10pt; FONT-WEIGHT: bold" rowSpan=2>Total $ amount per day</TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: Verdana; FONT-SIZE: 12pt; FONT-WEIGHT: bold">$11.50</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: Verdana; FONT-SIZE: 12pt; FONT-WEIGHT: bold">1</TD><TD style="BACKGROUND-COLOR: #ccffcc; FONT-FAMILY: Verdana; FONT-SIZE: 10pt; FONT-WEIGHT: bold">Total Hours Worked</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc; FONT-FAMILY: Verdana; FONT-SIZE: 10pt; FONT-WEIGHT: bold">08:00</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc; FONT-FAMILY: Verdana; FONT-SIZE: 9pt; FONT-WEIGHT: bold">04:00</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc; FONT-FAMILY: Verdana; FONT-SIZE: 9pt; FONT-WEIGHT: bold">12:00</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="BACKGROUND-COLOR: #ccffcc; FONT-FAMILY: Verdana; FONT-SIZE: 10pt; FONT-WEIGHT: bold">Week Day</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="BACKGROUND-COLOR: #ccffcc; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Monday</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">15:45</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">08:00</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">04:00</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">03:45</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">$247.25</TD></TR></TBODY></TABLE>


<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 83px"><COL style="WIDTH: 104px"><COL style="WIDTH: 154px"><COL style="WIDTH: 74px"><COL style="WIDTH: 111px"><COL style="WIDTH: 99px"><COL style="WIDTH: 181px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc; FONT-FAMILY: Verdana; FONT-SIZE: 10pt; FONT-WEIGHT: bold">Rate</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc; FONT-FAMILY: Verdana; FONT-SIZE: 10pt; FONT-WEIGHT: bold">Per (Hours)</TD><TD></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc; FONT-FAMILY: Verdana; FONT-SIZE: 10pt; FONT-WEIGHT: bold">Straight</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc; FONT-FAMILY: Verdana; FONT-SIZE: 8pt; FONT-WEIGHT: bold">Over Time X 1.5</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc; FONT-FAMILY: Verdana; FONT-SIZE: 8pt; FONT-WEIGHT: bold">Over Time X 2</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc; FONT-FAMILY: Verdana; FONT-SIZE: 10pt; FONT-WEIGHT: bold" rowSpan=2>Total $ amount per day</TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: Verdana; FONT-SIZE: 12pt; FONT-WEIGHT: bold">$500.00</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: Verdana; FONT-SIZE: 12pt; FONT-WEIGHT: bold">10</TD><TD style="BACKGROUND-COLOR: #ccffcc; FONT-FAMILY: Verdana; FONT-SIZE: 10pt; FONT-WEIGHT: bold">Total Hours Worked</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc; FONT-FAMILY: Verdana; FONT-SIZE: 10pt; FONT-WEIGHT: bold">10:00</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc; FONT-FAMILY: Verdana; FONT-SIZE: 9pt; FONT-WEIGHT: bold">02:00</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc; FONT-FAMILY: Verdana; FONT-SIZE: 9pt; FONT-WEIGHT: bold">12:00</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="BACKGROUND-COLOR: #ccffcc; FONT-FAMILY: Verdana; FONT-SIZE: 10pt; FONT-WEIGHT: bold">Week Day</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="BACKGROUND-COLOR: #ccffcc; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Monday</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">15:45</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">10:00</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">02:00</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">03:45</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">$1,025.00</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>D4</TD><TD>=MEDIAN(0,$D$2,C4)</TD></TR><TR><TD>E4</TD><TD>=MEDIAN(0,C4-D4,$E$2)</TD></TR><TR><TD>F4</TD><TD>=MEDIAN(0,C4-D4-E4,$F$2)</TD></TR><TR><TD>G4</TD><TD>=MAX($A$2,SUMPRODUCT((D4:F4*24)*({1,1.5,2})*($A$2/$B$2)))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Both examples use the same formula, just the rates / hours in row 2 have been changed as appropriate.

 

[serge]

Thank you for the great help, this is all what I needed for my 2 different payroll.

Really appreciate it.

Serge.

 

[serge]

I have a small problem !!!

What formatting should I use in cell C4 ? because if I want to put 25:00 hours, when I enter it show 1 and in G4 the $ amount show $ 1,850.00 ? which is incorrect for 25 hours.

also if I enter 0 in C4 , G4 show 500.00 ?

Is there a way to fix it ?

Thank you.