Excel timesheet. Public holiday pay conditional on precise conditions being met

Question

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 8s 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.

Answer 1

I believe mtone has basically got it, except for the bit about being paid for the holiday (for no work) if you work on Tuesday and Thursday.  So, changing to use user193896’s suggested cell IDs (but keeping mtone’s idea that B14 might be a Boolean flag to identify holidays), the pay for Wednesday should be

=IF(AND(B14, F13>0, F15>0), 8+F14*1.5, F14)

multiplied by the normal hourly rate of $4 an hour.

Answer 2

It would help to see the layout of your spreadsheet, but I'll assume for this answer that you're using a top-to-bottom calendar like this.

To achieve your goal, we'll create a column called "Holiday Multiplication Factor" formula that will contain either 1 (standard pay) or 1.5 (holiday pay) based on your conditions. You'll also need a column to (manually) indicate which days are holidays.

1   (A) Date    (B) Holiday?  (C) Hours  (D) Factor      (E) Paid
2   2013-01-30  FALSE         8          1               8 * 1 * $4 = 32
3   2013-02-01  TRUE          4          1.5             4 * 1.5 * $4 = 24
4   2013-02-02  FALSE         8          1               8 * 1 * 4$ = 32

To keep the formulas simple, we need to simplify your conditions as much as possible. If I understand you correctly, employees are always paid at regular rate (factor 1) on non-holiday days. Also employees not working on holidays are never paid for it (factor 1 is fine since 0 hours * 1 = 0).

So the only case we have left where the factor must be different than 1 is if A) it's a holiday and B) some hours have been worked before the day prior AND after, in which case the factor must be 1.5. Simple enough! Now we can translate that into a formula with those 2 conditions.

Let's put this in D3. The formula checks if it's a holiday (B3) and the hours worked above and below (C2 and C4) that day.

=IF(B3, IF(AND(C2,C4), 1.5, 1), 1)

So that's 2 IF statements, the first (outer) one being:

=IF(B3....    

So if B3 is FALSE (not a holiday), it will always return 1, however if it's true, it will then calculate the second condition in the nested IF:

=IF(AND(C2,C4).....   

AND checks the hours worked (column C) and returns TRUE if both values are TRUE. Note that I'm using a shortcut by saying C2 instead of C2>0 - it works because 0 is always FALSE and any other number is TRUE. If both are TRUE we'll get the 1.5 result. But if one or both days have no hours, it returns the usual 1.

Then you can copy this formula everywhere (except perhaps the first row since you don't have information on the day prior) and it should always be 1 or 1.5 where appropriate.

Finally, when calculating your Pay, just multiply HOURS * FACTOR * PAY RATE.

Edit: Here would be a "simplified" formula combining all the above into one single PAID column formula, assuming a 4$ salary at the end. Thanks to Scott for his helpful comment about not needing 2 IF statements. To be put in E3 and copied everywhere after:

=IF(AND(B3,C2>0,C4>0), C3*1.5, C3))*4

Edit 2: I missed part of the question where 8 extra standard-pay hours would be awarded if the employee worked on the day before and after. Scott provides a correct solution including that into account.