+ Reply to Thread
Results 1 to 15 of 15

Payroll formula helped needed to pay differential on Sat and Sunday

  1. #1
    Registered User
    Join Date
    05-22-2012
    Location
    Boise, Idaho
    MS-Off Ver
    Excel 2007
    Posts
    39

    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
    Attached Files Attached Files
    Last edited by mrteater; 08-07-2019 at 09:20 PM. Reason: updated attachment again

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    10,503

    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.
    Last edited by protonLeah; 08-05-2019 at 04:50 PM.
    Ben Van Johnson

  3. #3
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2019
    Posts
    15,963

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

    Administrative Note:

    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. #4
    Registered User
    Join Date
    05-22-2012
    Location
    Boise, Idaho
    MS-Off Ver
    Excel 2007
    Posts
    39

    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 )

  5. #5
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    10,503

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

    removed.....
    Last edited by protonLeah; 08-12-2019 at 10:51 PM.

  6. #6
    Forum Guru Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,090

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

    Seems awfully kludgy, but so do your rules, so how about

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    05-22-2012
    Location
    Boise, Idaho
    MS-Off Ver
    Excel 2007
    Posts
    39

    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)
    Attached Files Attached Files
    Last edited by mrteater; 08-07-2019 at 09:19 PM.

  8. #8
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    7,869

    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: copy to clipboard
    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.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  9. #9
    Registered User
    Join Date
    05-22-2012
    Location
    Boise, Idaho
    MS-Off Ver
    Excel 2007
    Posts
    39

    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. #10
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    7,869

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

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

    Let us know if you have any questions.

  11. #11
    Forum Guru Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,090

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

    Quote Originally Posted by mrteater View Post
    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. #12
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    7,869

    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: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.

  13. #13
    Registered User
    Join Date
    05-22-2012
    Location
    Boise, Idaho
    MS-Off Ver
    Excel 2007
    Posts
    39

    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. #14
    Forum Guru Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,090

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

    Quote Originally Posted by mrteater View Post
    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. #15
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    7,869

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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