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

 

[jasonb75]

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.

Last time I checked there were only 24 hours in a day so anything more will cause the formula to break, to work around this you would need to increase the cap in column F (the cap appears as 12:00 either in F2 or in the formula in F4 depending on which setup you're using).

Use custom format [h]:mm on C4

Use

=IF(C4,MAX($B$2,SUMPRODUCT((D4:F4*24)*({1,1.5,2})*($B$2/10))),0)

to give a zero result when there is nothing in C4.

 

[serge]

I had to ask you about going over 24 hours, because in my job especially music video we go over 24 hours straight work sometime, so I wanted to see if the formula could had calculate it in the same line !!! and the formula you provided for the 0 work fine.

Thank you very much for the time card formulas and to finalized it, this is perfect now, because of you.

Thanks again.
Serge.