I'm trying to make an Excel timesheet that does some quite particular functions.

1) If we say Wednesday is a public holiday, if the employee worked on Tuesday AND Thursday (days on either side of the public holiday) BUT NOT on Wednesday (the public holiday) he would be entitled to a standard day’s pay (8 hours at normal rate) e.g., if his normal hourly rate is $4 an hour, he would be paid $32 for Wednesday even though he did not work on it.

2) If the employee worked on Tuesday AND Thursday (days on either side of the public holiday) AND ALSO on Wednesday (the public holiday), he would be entitled to a standard day’s pay (8 hours at normal rate) plus the number of hours he worked on Wednesday at 1.5 times the normal rate. e.g., if his normal hourly rate is $4 an hour, he would be paid $32 for the public holiday PLUS if he worked 5 hours on the public holiday, he would get 5 hours at $6 an hour (1.5 times his normal hourly rate) SO... his day’s pay would be 8 standard hours at $4 an hour = $32 PLUS 5 holiday pay hours at $6 an hour = $30.

So a total of $62.

3) If the employee DID NOT work on Tuesday BUT worked Wednesday (the public holiday) AND Thursday, he would just get paid the standard hourly rate for the number of hours worked. So, if he worked 5 hours on the public holiday he would get 5 standard hours at $4 an hour = $20.

4) If the employee DID NOT work on Thursday BUT DID work on Tuesday AND Wednesday (the public holiday), he would just get paid the standard hourly rate for the number of hours worked. So, if he worked 5 hours on the public holiday, he would get 5 standard hours at $4 an hour = $20.

5) If the employee did not work on Tuesday OR Thursday and ALSO DID NOT work on Wednesday (the public holiday), he would not get paid for any of those days.

I hope I have not confused you!!!

Suggested rewrite:

Say Wednesday is a public holiday.

1) If an employee worked on both Tuesday AND Thursday (days on either side of the public holiday), he is entitled to a standard day’s pay (8 hours at his normal rate) ** for the holiday** (e.g., if his normal rate is $4 an hour, he would be paid $32) PLUS pay for any hours he worked on Wednesday at 1.5 times the normal rate (e.g., if he worked 5 hours on the holiday, he would get 5 hours × $6 an hour = $30).

2) If the employee DID NOT work on both Tuesday and Thursday, he would just get paid the standard (normal) hourly rate for any hours he worked on Wednesday. So, if he worked 5 hours on the holiday, he would get 5 hours × $4 an hour = $20. (And if he didn’t work the holiday, he wouldn’t get any pay for it.)

For example (shown transposed, with rows–> columns and columns –> rows, for clarity):

F13 F14 F15 Pay for Wednesday^{(Tuesday Hours worked) (Wednesday Hours worked) (Thursday Hours worked)}0 0 0 0 0 0 8 0 0 5 0 5× $4 = $20 0 5 8 5× $4 = $20 8 0 0 0 8 0 8 8×$4 = $32 8 5 0 5× $4 = $20 8 5 8 8×$4 + 5×1.5×$4 = $62

The `8`

s are arbitrary –– in principle, the bottom row could be `1 5 1`

and the pay *for Wednesday* would be the same.

Any help would be really appreciated, as I am new to using formulas in Excel, and I easily confuse myself!!

I have tried using IF AND statements, but am not sure how to make them sufficiently complex to do what I am trying to achieve.

I have tried:

Where cell F13 is Tuesday Hours worked, F14 is Wednesday Hours worked, F15 is Thursday Hours worked.

=IF(AND(F13>0,F14>0,F15>0),F14,"Standard day rate")

This formula can work out how many PUBLIC Holiday hours pay to give if all 3 days are worked.... but not much else.

Replying in commentsparagraphs of theComment formattingsection of the Markdown Editing Help page. – Scott Feb 1 '13 at 22:41