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
Last edited by mrteater; 08-07-2019 at 09:20 PM. Reason: updated attachment again
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
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.)
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
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.....
Last edited by protonLeah; 08-12-2019 at 10:51 PM.
Seems awfully kludgy, but so do your rules, so how about
Formula:
Please Login or Register to view this content.
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)
Last edited by mrteater; 08-07-2019 at 09:19 PM.
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.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
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)
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.
If they work over 8 hours on Sat, does that excess get 1.5*rate, 1.5*rate + $1, or just + $1?Originally Posted by mrteater
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.
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
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?)
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
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks