'if' formula help needed

[pohteck89]

Hi Guys,


I need help in this formula where i can auto calculate the total amount worked


Rules:
Office Hours 8.30am to 6.00pm - Rate is $7.50 per 15min block
After office hours 6.00pm onwards -Rate is $15 per $30min block


Examples
During office hours, 2.00pm - 5.15pm. Total amount will be $97.50 (3hrs 15mins)
During office hours, 2.00pm - 5.10pm. Total amount will be $97.50 (3hrs 10mins) *total amount is the same because it is within the 15mins block


After office hours, 2.00pm - 6.45pm. Total amount will be $150 (4hr 45mins) *from 2.00pm-6.00pm is $120. From 6.00pm to 6.45pm is $30, because 6.00pm-6.30pm is $15,6.30pm-6.45pm is $15 also as it is within the 30mins block


Anyone can help with this formula?

 

[drsarao]

Try this:

Excel Workbook
	A	B	C	D	E	F	G	H
1	Start	End	Minutes Before Cutoff	Minutes After Cutoff	Total$
2	14:00	17:15	195	0	97.50		Work hrs rate	7.5
3	14:00	17:10	190	0	97.50		Overtime rate	15
4	14:00	18:45	240	45	150.00		Cutoff time	18:00
5	14:00	18:00	240	0	120.00
6	18:00	18:45	0	45	30.00
Sheet1

Excel 2003

Cell Formulas
Range	Formula
C2	=IF(B2>$H$4,HOUR($H$4-A2)*60+MINUTE($H$4-A2),HOUR(B2-A2)*60+MINUTE(B2-A2))
D2	=IF(B2>$H$4,HOUR(B2-$H$4)*60+MINUTE(B2-$H$4),0)
E2	=INT(C2/15)*$H$2+IF(MOD(C2,15),$H$2) + INT(D2/30)*$H$3+IF(MOD(D2,30),$H$3)