SOLVED

Formula calculating earnings during one shift when three different rates of pay apply

%3CLINGO-SUB%20id%3D%22lingo-sub-1476822%22%20slang%3D%22en-US%22%3EFormula%20calculating%20earnings%20during%20one%20shift%20when%20three%20different%20rates%20of%20pay%20apply%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1476822%22%20slang%3D%22en-US%22%3E%3CTABLE%20width%3D%22333%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2277%22%3E%3CP%3EStart%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2277%22%3EEnd%3C%2FTD%3E%3CTD%20width%3D%2277%22%3EHours%3C%2FTD%3E%3CTD%20width%3D%22102%22%3EEarnings%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E10%3A00%3A00%3C%2FTD%3E%3CTD%3E20%3A00%3A00%3C%2FTD%3E%3CTD%3E10%3A00%3A00%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E08%3A00%3A00%3C%2FTD%3E%3CTD%3E18%3A00%3A00%3C%2FTD%3E%3CTD%3E10%3A00%3A00%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E15%3A00%3A00%3C%2FTD%3E%3CTD%3E00%3A00%3A00%3C%2FTD%3E%3CTD%3E09%3A00%3A00%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E10%3A00%3A00%3C%2FTD%3E%3CTD%3E20%3A00%3A00%3C%2FTD%3E%3CTD%3E10%3A00%3A00%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E10%3A00%3A00%3C%2FTD%3E%3CTD%3E20%3A00%3A00%3C%2FTD%3E%3CTD%3E10%3A00%3A00%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E14%3A00%3A00%3C%2FTD%3E%3CTD%3E22%3A00%3A00%3C%2FTD%3E%3CTD%3E08%3A00%3A00%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E14%3A00%3A00%3C%2FTD%3E%3CTD%3E22%3A00%3A00%3C%2FTD%3E%3CTD%3E08%3A00%3A00%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E23%3A00%3A00%3C%2FTD%3E%3CTD%3E08%3A30%3A00%3C%2FTD%3E%3CTD%3E09%3A30%3A00%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E10%3A00%3A00%3C%2FTD%3E%3CTD%3E20%3A00%3A00%3C%2FTD%3E%3CTD%3E10%3A00%3A00%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E10%3A00%3A00%3C%2FTD%3E%3CTD%3E20%3A00%3A00%3C%2FTD%3E%3CTD%3E10%3A00%3A00%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E22%3A00%3A00%3C%2FTD%3E%3CTD%3E08%3A30%3A00%3C%2FTD%3E%3CTD%3E10%3A30%3A00%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E22%3A00%3A00%3C%2FTD%3E%3CTD%3E08%3A30%3A00%3C%2FTD%3E%3CTD%3E10%3A30%3A00%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20rates%20of%20pay%20are%3A%3C%2FP%3E%3CTABLE%20width%3D%22264%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2288%22%3ERate%20of%20Pay%3C%2FTD%3E%3CTD%20width%3D%2288%22%3EStart%3C%2FTD%3E%3CTD%20width%3D%2288%22%3EEnd%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%C2%A310%3C%2FTD%3E%3CTD%3E07%3A00%3A00%3C%2FTD%3E%3CTD%3E19%3A00%3A00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%C2%A315%3C%2FTD%3E%3CTD%3E19%3A00%3A00%3C%2FTD%3E%3CTD%3E22%3A00%3A00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%C2%A320%3C%2FTD%3E%3CTD%3E22%3A00%3A00%3C%2FTD%3E%3CTD%3E07%3A00%3A00%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20for%20instance%2C%20if%20someone%20worked%2015%3A00%3A00%20-%2000%3A00%3A00%20they%20would%20be%20paid%20%C2%A3125%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B-%2015%3A00%3A00%20-%2019%3A00%3A00%26nbsp%3B%40%C2%A310%2Fhr%20%3D%20%C2%A340%3C%2FP%3E%3CP%3E%26nbsp%3B-%2019%3A00%3A00%20-%2022%3A00%3A00%26nbsp%3B%40%C2%A315%2Fhr%20%3D%20%C2%A345%3C%2FP%3E%3CP%3E%26nbsp%3B-%2022%3A00%3A00%20-%2000%3A00%3A00%26nbsp%3B%40%C2%A320%2Fhr%20%3D%20%C2%A340%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20managed%20to%20come%20up%20with%20a%20formula%20that%20will%20calculate%20number%20of%20hours%20worked%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(AC19%26gt%3BAB19%2CAC19-AB19%2C1-AB19%2BAC19)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20a%20formula%20that%20will%20tell%20me%20how%20much%20is%20earned%20during%20each%20shift.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1476822%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1478266%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20calculating%20earnings%20during%20one%20shift%20when%20three%20different%20rates%20of%20pay%20apply%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1478266%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F704604%22%20target%3D%22_blank%22%3E%40alex201280%3C%2FA%3E%26nbsp%3BPlease%20see%20the%20attached%20workbook%20for%20suggestions.%3C%2FP%3E%3CP%3EI%20do%20not%20think%20that%20your%20problem%20can%20be%20solved%20with%20just%20a%20few%20formulas%20because%20there%20are%20a%20lot%20of%20possible%20cases.%20It%20would%20be%20easier%20if%20you%20have%20some%20umbrella%20rules%20such%20as%20the%20total%20consecutive%20hours%20in%20a%20shift%20cannot%20exceed%20a%20certain%20number%20of%20hours%2C%20etc.%20Anyhow%2C%20with%20a%20bunch%20of%20helper%20columns%20(which%20you%20could%20hide)%2C%20the%20good%20news%20is%20that%20all%20my%20testing%20cases%20return%20correct%20results.%20I%20may%20not%20have%20tested%20all%20possible%20scenarios%20so%20let%20me%20know%20of%20any%20case%20it%20does%20not%20work.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1478692%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20calculating%20earnings%20during%20one%20shift%20when%20three%20different%20rates%20of%20pay%20apply%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1478692%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F704604%22%20target%3D%22_blank%22%3E%40alex201280%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFirst%20I'd%20define%20rates%20for%20this%20and%20next%20days%20as%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20580px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F199879i33031E5AA7288FD1%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3ENext%20day%20is%20in%20bold%20here.%3C%2FP%3E%0A%3CP%3EWith%20that%20formula%20could%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DSUMPRODUCT(%0A%20%20IF(%24B3%26gt%3B%24H%243%3A%24H%247%2C0%2C%0A%20%20%20%20%20%20IF(%24G%243%3A%24G%247%26gt%3B(%24C3%2B1*(%24C3%26lt%3B%24B3))%2C%200%2C%0A%20%20%20%20%20%20%20%20%20%20IF((%24C3%2B1*(%24C3%26lt%3B%24B3))%26gt%3B%24H%243%3A%24H%247%2C%24H%243%3A%24H%247%2C(%24C3%2B1*(%24C3%26lt%3B%24B3)))-%0A%20%20%20%20%20%20%20%20%20%20IF(%24B3%26gt%3B%24G%243%3A%24G%247%2C%24B3%2C%24G%243%3A%24G%247)%0A%20%20%20%20%20)%20)*%24I%243%3A%24I%247)*24%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1479106%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20calculating%20earnings%20during%20one%20shift%20when%20three%20different%20rates%20of%20pay%20apply%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1479106%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBrilliant.%20Thank%20you!%20Much%20appreciated%20indeed.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1479370%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20calculating%20earnings%20during%20one%20shift%20when%20three%20different%20rates%20of%20pay%20apply%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1479370%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F704604%22%20target%3D%22_blank%22%3E%40alex201280%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Start

EndHoursEarnings
10:00:0020:00:0010:00:00 
08:00:0018:00:0010:00:00 
15:00:0000:00:0009:00:00 
10:00:0020:00:0010:00:00 
10:00:0020:00:0010:00:00 
14:00:0022:00:0008:00:00 
14:00:0022:00:0008:00:00 
23:00:0008:30:0009:30:00 
10:00:0020:00:0010:00:00 
10:00:0020:00:0010:00:00 
22:00:0008:30:0010:30:00 
22:00:0008:30:0010:30:00 

 

The rates of pay are:

Rate of PayStartEnd
£1007:00:0019:00:00
£1519:00:0022:00:00
£2022:00:0007:00:00

 

So for instance, if someone worked 15:00:00 - 00:00:00 they would be paid £125: 

 - 15:00:00 - 19:00:00 @£10/hr = £40

 - 19:00:00 - 22:00:00 @£15/hr = £45

 - 22:00:00 - 00:00:00 @£20/hr = £40

 

I've managed to come up with a formula that will calculate number of hours worked: 

=IF(AC19>AB19,AC19-AB19,1-AB19+AC19)

 

I need a formula that will tell me how much is earned during each shift.

 

Many thanks.

4 Replies
Highlighted

@alex201280 Please see the attached workbook for suggestions.

I do not think that your problem can be solved with just a few formulas because there are a lot of possible cases. It would be easier if you have some umbrella rules such as the total consecutive hours in a shift cannot exceed a certain number of hours, etc. Anyhow, with a bunch of helper columns (which you could hide), the good news is that all my testing cases return correct results. I may not have tested all possible scenarios so let me know of any case it does not work.

Highlighted
Best Response confirmed by alex201280 (New Contributor)
Solution

@alex201280 

First I'd define rates for this and next days as

image.png

Next day is in bold here.

With that formula could be

=SUMPRODUCT(
  IF($B3>$H$3:$H$7,0,
      IF($G$3:$G$7>($C3+1*($C3<$B3)), 0,
          IF(($C3+1*($C3<$B3))>$H$3:$H$7,$H$3:$H$7,($C3+1*($C3<$B3)))-
          IF($B3>$G$3:$G$7,$B3,$G$3:$G$7)
     ) )*$I$3:$I$7)*24
Highlighted

@Sergei Baklan 

 

Brilliant. Thank you! Much appreciated indeed.

Highlighted