Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
1  
Welcome to SuperUser! While we are here to help, we can't usually do everything for you. Please expand your answer to tell us what have you tried, what worked/didn't work, or what errors are you seeing. –  techturtle Jan 31 '13 at 23:10
    
Well, one of us is confused. I don’t see where you’ve said what should happen if the employee worked either Tuesday OR Thursday but not both. –  Scott Jan 31 '13 at 23:24
    
Scott - Unless I'm really confusing myself, points 3 and 4 cover that. My "neither" can be switched for your "either"!! –  user193896 Feb 1 '13 at 0:23
    
Well, one of us is still confused –– “either” and “neither” are not interchangeable. “They will be serving either burgers or chicken at the picnic.” “They will be serving neither burgers nor chicken at the picnic.” I’m going to the first picnic! // OK, I just realized that I overlooked the fact that you said “neither Tuesday OR Thursday”. But that doesn’t help –– “neither … OR …” is ungrammatical. So I don’t know what you mean by paragraphs 3 and 4, and I don’t have an answer to my question. –  Scott Feb 1 '13 at 20:13
    
Also, I’ll reiterate @techturtle’s greeting: welcome to Super User. For your information, when you respond to a comment (in a new comment), it’s conventional to mention the author’s name, preceded by “@”, as in “@Scott”. That way he gets notified. You can mention multiple names, as in “@mtone, @Scott”, but there may be a limit of two or three names. See the Replying in comments paragraphs of the Comment formatting section of the Markdown Editing Help page. –  Scott Feb 1 '13 at 22:41

2 Answers 2

up vote 1 down vote accepted

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.

share|improve this answer
    
You're completely right, I misread that part of the question. –  mtone Feb 2 '13 at 2:06

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.

share|improve this answer
    
Thanks so much for your efforts in trying to help me!!! I am trying to implement your solution now and see if it works across all my scenarios. Thanks once again for your careful, thought out explanation –  user193896 Feb 1 '13 at 0:30
1  
How is =IF(B3, IF(AND(C2>0,C4>0), 1.5, 1), 1) different from =IF(AND(B3,C2>0,C4>0), 1.5, 1)? –  Scott Feb 1 '13 at 20:20
    
@Scott +1, that would work too! I just kept the conditions separate for reflect my 2-step thought process, but your suggestion is more readable in the end. –  mtone Feb 1 '13 at 20:50
    
@mtone Now, I look at it, apart from the true and false field your solution is not that different from mine.... and neither quite do what I need yet. –  user193896 Feb 2 '13 at 0:02

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.