# Payroll formula helped needed to pay differential on Sat and Sunday

1. ## Payroll formula helped needed to pay differential on Sat and Sunday

I need help on the formula so that it will identify based off of the date so it pay \$1 extra per hours worked on Saturday and time and a half for hours worked on Sunday. See the attached sheet. The pay rate is in column C. The date are in row 2 with the daily pay totals are in columns starting with E then G then I... I need help with updating the formula if possible or another idea to make this happen.

Crosspost:
https://www.excelguru.ca/forums/show...Sat-and-Sunday

2. ## Re: Payroll formula helped needed to pay differential on Sat and Sunday

Original G3:
=IF( F3 > 480, ((F3-480)*\$C3*1.5 / 60) + (\$C3/60 * 480 ), SUM(\$C3/60)*F3 )
modified to test if weekday is saturday=7 or sunday=1
``Please Login or Register  to view this content.``

3. ## Re: Payroll formula helped needed to pay differential on Sat and Sunday

Welcome to the forum.

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

(Note: this requirement is not optional. No help to be offered until the link is provided.)

4. ## Re: Payroll formula helped needed to pay differential on Sat and Sunday

I have update the sheet with this suggested formula ( =IF( F3 > 480, ((F3-480)*(IF(WEEKDAY(F2)=7,\$C3+1,IF(WEEKDAY(F\$3)=1,\$C3*1.5,\$C3))) / 60) + (\$C3/60 * 480 ), SUM(\$C3/60)*F3 ) ) but still it does not work. The shifts on Saturday and Sunday will not exceed 480 minutes but they still get paid the differential. examples I placed in Columns L and N. IF they work one hour (60minutes) the differential should kick in.

In M3 the formula is
=IF( L3 > 480, ((L3-480)*(IF(WEEKDAY(L2)=7,\$C3+1,IF(WEEKDAY(L\$2)=1,\$C3*1.5,\$C3))) / 60) + (\$C3/60 * 480 ), SUM(\$C3/60)*L3 )[/COLOR]
In O3 the formula is
=IF( N3 > 480, ((N3-480)*(IF(WEEKDAY(N2)=7,\$C3+1,IF(WEEKDAY(N\$2)=1,\$C3*1.5,\$C3))) / 60) + (\$C3/60 * 480 ), SUM(\$C3/60)*N3 )

removed.....

6. ## Re: Payroll formula helped needed to pay differential on Sat and Sunday

Formula:
`Please Login or Register  to view this content.`

7. ## Re: Payroll formula helped needed to pay differential on Sat and Sunday

I am using the following formula and it works great for Sunday differential but it does nto change for saturday differtnial. It stays at \$13.00 no matter how many minutes I place in L3. It is also giving the 1.5 bonus for regular weekdays. I am open to another type of formula if you have a suggestion. My end goal is to pay employees \$1 an hour extra for Saturday work and \$1.5 extra for Sunday work (per hour).
=ROUND(IF(WEEKDAY(L\$2)=7,IF(L3>480,8*(\$C3+1)+((L3-480)/60)*(\$C3*1.5+1),\$C3+1),IF(WEEKDAY(L\$2)=1,\$C3*1.5*L3/60,\$C3*IF(L3>480,(480+(L3-480)*1.5)/L3,1)*L3/60)),2)

8. ## Re: Payroll formula helped needed to pay differential on Sat and Sunday

Since Saturday's and Sunday's time will not exceed 480 minutes try:
Formula:
`Please Login or Register  to view this content.`

I imagine that one source of confusion is that "...\$1.5 extra for Sunday work (per hour)" is not the same as "...time and a half for hours worked on Sunday."
Let us know if you have any questions.

9. ## Re: Payroll formula helped needed to pay differential on Sat and Sunday

Sorry for any confusion I have genereated. I have listed out the specifics again of what I need the formula to do

1. We will pay regular rate for time worked on weekday under 480 minutes (8 hours)
2. We pay regular rate*1.5 (Time and a half like overtime pay) for time over 480 minutes (8 hours)
3. We pay regular rate +\$1Hr extra differential pay for time worked on Saturday
4. We pay regular rate*1.5 (Time and a half like overtime pay)for time on Sunday. So if they exceeded 8 hours on Sunday that would be 3* rate (if my math is correct although that has never happened)

10. ## Re: Payroll formula helped needed to pay differential on Sat and Sunday

I believe that the following formula meets the requirements:
Formula:
`Please Login or Register  to view this content.`

Let us know if you have any questions.

11. ## Re: Payroll formula helped needed to pay differential on Sat and Sunday

Originally Posted by mrteater
Sorry for any confusion I have genereated. I have listed out the specifics again of what I need the formula to do

1. We will pay regular rate for time worked on weekday under 480 minutes (8 hours)
2. We pay regular rate*1.5 (Time and a half like overtime pay) for time over 480 minutes (8 hours)
3. We pay regular rate +\$1Hr extra differential pay for time worked on Saturday
4. We pay regular rate*1.5 (Time and a half like overtime pay)for time on Sunday. So if they exceeded 8 hours on Sunday that would be 3* rate (if my math is correct although that has never happened)
If they work over 8 hours on Sat, does that excess get 1.5*rate, 1.5*rate + \$1, or just + \$1?

12. ## Re: Payroll formula helped needed to pay differential on Sat and Sunday

I imagine that Bob has brought up a good point and that the formula in cell E3 should read:
Formula:
`Please Login or Register  to view this content.`

Let us know if you have any questions.

13. ## Re: Payroll formula helped needed to pay differential on Sat and Sunday

Thank you very much. The answer to the question is yes if they exceed 480minutes (8 hours in one day) they also get overtime plu \$1 differential for each hour. It looks like you have got it. Again, thank you very much

14. ## Re: Payroll formula helped needed to pay differential on Sat and Sunday

Originally Posted by mrteater
The answer to the question is yes if they exceed 480minutes (8 hours in one day) they also get overtime plu \$1 differential for each hour.
As more than 8 hours is rate * 1.5, and Sun is rate * 1.5, is Sun more than 8 hours rate * 1.5 * 1.5, or rate * 2.25 (or are we going too far now? )

15. ## Re: Payroll formula helped needed to pay differential on Sat and Sunday

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1