Checking for the days in a column

[gloom52]

Work Schedule and Salary Calculation Extreme.xls

I have a spreadsheet as a rough guide to calculate the pay of a shift worker but im a newbie in excel.
I've previously sought help from this forum and thought I could do it again.

Taking an example worksheet: "Jun12"
Column A is where all the dates are, Column B belongs to the shift pattern, Column C&D is the start and end time of work and E/F/G are the overtime perks.
Recently theres this change in the payment whereby, if "R" is on a Sunday, the following day will be a x2 rate.
So what I wish is a formula that can do a check on this eg: If(And(Cell Axx-1=Sunday,Cell Axx-1="R"),2,usual formula). This will be true for Column E's calculation.
For Column F&G it will be : If(And(Cell Axx-1=Sunday,Cell Axx-1="R"),0,usual formula).

[Cutter]

Try something like this for E5 and down:

=IF(AND(B4="R",WEEKDAY(A5)=2),2,usual formula)

you will need a variation for E4 because you'll need to look on the previous month's sheet to check for the "R"

[ben_hensel]

Your question is pretty buried in there, but if I understand it, you want a way to test whether a date falls on a Sunday?

That's actually pretty straight-forward: use the WEEKDAY() function.

= WEEKDAY(A1, 1)

will return a value of 1 for Sundays, 2 for Monday, 3 for Tuesday, up to 6 for Saturday.

That what you needed?

[gloom52]

Thanks for the help but I thought my workbook could use a major revamp cause i think its too messy.
Basically the method of calculating the overtime is this:
Off days:
First 4 hrs = 1.5 rate, Subsequent = 2 rate
Rest days:
If work is less than 6hrs = 1 day pay, more than 6 = 2 day pay

For night shifts:
it usually starts from 8pm to 8am in the next morning, so if its on a "W" (which really is an "O"):
First 4 hrs = 1.5, subsequent 8 hrs = 2 day pay due to it falling on a "R" when it crosses to another day and also because its more than 6hrs

If the night shift is on a "R":
first 4 hrs = 1 day pay due to <6hs, subsequent 8hrs = 2 rate because its on a "O" the following day.

Then theres also this golden monday whereby it will be considered like a "R". If the previous day was a Sunday and is a "R", the Monday will be 2 day pay if its more than 6hrs or 1day pay if its less than 6hrs.

Please advise thanks