calculate wage per rate?

[tezza]

Hi all

Any idea how I go about calculating wages that are based on time rates as per example below.

I'm just not sure how to automatically work it out if the hours go through different pay rates as in D7 through F9

RATE 1 = 9AM - 8PM MON-FRI
RATE 2 = 8PM - 9AM MON-THUR
RATE 3 = FRI 8PM - MON 9AM

Excel Workbook
	A	B	C	D	E	F	G
2	*	*	*	x1	x1.25	x1.5	*
3	*	RATE	6.64	RATE 1	RATE 2	RATE 3	*
4	*	*	*	6.64	8.3	9.96	*
5	*	*	*	*	*	*	*
6	DAY	START	END	DAY	NIGHT	WEEKEND	TOTAL
7	Mon	06:00	14:00	*	*	*	*
8	Wed	14:00	22:00	*	*	*	*
9	Fri	17:00	02:00	*	*	*	*
Sheet1

Regards

 

[schielrn]

What would be considered day as opposed to night as opposed to weekend? Also I would assume that 8PM on a Firday is considered weekend or is it considered night? I think we need more detail or how day, night and weekend are broken into categories.

 

[tezza]

Make that

RATE 1 = 9AM - 8PM MON-FRI
RATE 2 = 8PM - 9AM MON-FRI
RATE 3 = FRI 8PM - MON 9AM

 

[schielrn]

Would this be your expected results with this data sample?

Excel Workbook
	A	B	C	D	E	F	G
6	DAY	START	END	DAY	NIGHT	WEEKEND	TOTAL
7	Mon	6:00	14:00	33.2	0	29.88	63.08
8	Wed	14:00	22:00	39.84	0	19.92	59.76
9	Fri	17:00	2:00	0	24.9	39.84	64.74
Sheet24

Cell Formulas
Range	Formula
D7	=6.64*5
D8	=6.64*6
D9	=6.64*0
E7	=8.3*0
E8	=8.3*0
E9	=8.3*3
F7	=9.96*3
F8	=9.96*2
F9	=9.96*4
G7	=SUM(D7:F7)
G8	=SUM(D8:F8)
G9	=SUM(D9:F9)

I just want to make sure I have everything correct before I ATTEMPT to solve this.

 

[tezza]

Had to work it out

You are close, wed doesn't fall on weekend

Fri til 8pm is still day rate

Excel Workbook
	A	B	C	D	E	F	G
2	*	*	*	x1	x1.25	x1.5	*
3	*	RATE	6.64	RATE 1	RATE 2	RATE 3	*
4	*	*	*	6.64	8.3	9.96	*
5	*	*	*	*	*	*	*
6	DAY	START	END	DAY	NIGHT	WEEKEND	TOTAL
7	Mon	06:00	14:00	33.2	*	29.88	63.08
8	Wed	14:00	22:00	39.84	16.6	*	56.44
9	Fri	17:00	02:00	19.92	*	49.8	69.72
Sheet1

 

[schielrn]

Yes, sorry I didn't look to closely and put some in the wrong columns , but I am unfortunately leaving the office now, but will check back on this first thing in the morning and see if I can come up with anything for it. Hopefully someone else can come along and help you. Barry Houdini is typically a go to guy on these types of things.

 

[tezza]

Any joy at all?

 

[tezza]

Is what I've shown enough or does it require better clarification?

regards

 

[tezza]

[Solved] calculate wage per rate?

Huge thanks to Toonies for the work put into getting this to work