is it possible

HappyChappy

Active Member
Joined
Jan 26, 2013
Messages
325
Office Version
  1. 2019
  2. 2010
  3. 2007
Platform
  1. Windows
Exel.xlsx
ABCDEFGHIJK
1JanuaryHours WorkedTotal HoursTotal Pay
2Sunday30/01/202200:0000:0000:000:00 
3Monday31/01/202210:0020:0010:009:15£92.50
4Tuesday01/02/202210:0020:0010:009:15£92.50
5Wednesday02/02/202210:0020:1510:159:30£95.00
6Thursday03/02/202210:0014:0004:004:00£80.00
7Friday04/02/202210:0020:0010:009:15£92.50
8Saturday05/02/202210:0016:0006:006:00£120.00
9
10
11RATES
12£10.00Standard RateMin 8 hrs paid as in G6 once 48 is reached Overtime kicks in to all paymnts
13£15.00O/Time Rate
14£12.00Saturday Rate
15£18.00Saturday O/Time Rate
16£15.00Sunday Rate
17£22.00Sunday O/Time Rate
18
Jan-22
Cell Formulas
RangeFormula
E2:E8E2=D2-C2
F2:F8F2=IF(E2>TIME(8,0,),E2-TIME(0,45,0),E2)
G2G2=IF(F2=0,"",MAX(Data!$B$44,F2*24)*$A$14)
G3:G7G3=IF(F3=0,"",MAX(Data!$B$44,F3*24)*$A$12)
G8G8=IF(F8=0,"",MAX(Data!$B$44,F8*24)*$A$16)
B3:B8B3=B2+1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D2:D3Cell Value>0textNO
D2:D3Cell Value>0textNO
D2:D3Cell Value>0textNO
D2:D3Cell Value>0textNO
D2:D3Cell Value>0textNO
D2:D3Cell Value>0textNO
C2:D10Cell Value>0textNO



Trying to work out how to build in hours work over 48 at overtime rate. The sheet can work out minimum hours works of 8hr also breaks of 45 mins need to be factored in once 8:45 is reached
i hope i have pasted the mini sheet correctly as it's my first attempt at this thank you
 

BSALV

Banned user
Joined
Oct 31, 2010
Messages
1,651
Office Version
  1. 365
  2. 2013
  3. 2007
i'm almost sure that's not possible, but i have no experience with mobile phones.
So, it's perhaps better to open a new question here on the site about that.
Maybe, somebody else knows a workaround ...
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

HappyChappy

Active Member
Joined
Jan 26, 2013
Messages
325
Office Version
  1. 2019
  2. 2010
  3. 2007
Platform
  1. Windows
Have been using your spread sheet and its been great but the company have decided to change things, they cant leave well alone.
Monday to friday now from 06:00-18:00 standard rate £18ph 18:00-06:00 £19ph this bit is easy to adjust but now they also want after 8 hours rate to be £22ph is this some thing that can be changed easily by reworking formula in column J
 

BSALV

Banned user
Joined
Oct 31, 2010
Messages
1,651
Office Version
  1. 365
  2. 2013
  3. 2007
??? those "48 normal hours / week" is history, now it's a "06:00-18:00" job with overtime before and after ?
And pause ?
 

HappyChappy

Active Member
Joined
Jan 26, 2013
Messages
325
Office Version
  1. 2019
  2. 2010
  3. 2007
Platform
  1. Windows
Yes the 48hr has been replaced by the new system. So if I started at 10:00 till 20:00 10:00-18:00 at £18ph 18:00-20:00 at £22 have just asked about the break deduction of .45 mins that comes off at the standard rate of £18ph
So overtime is worked out anything over 8 hrs
 

BSALV

Banned user
Joined
Oct 31, 2010
Messages
1,651
Office Version
  1. 365
  2. 2013
  3. 2007
is the break 12:00-12:45 or 1/2 hour after 4 hours or ...
if it's too difficult, it'll be an UDF, so not suitable on a mobile phone.
 

HappyChappy

Active Member
Joined
Jan 26, 2013
Messages
325
Office Version
  1. 2019
  2. 2010
  3. 2007
Platform
  1. Windows
is the break 12:00-12:45 or 1/2 hour after 4 hours or ...
if it's too difficult, it'll be an UDF, so not suitable on a mobile phone.
Break can be anytime in the shift and is deducted at .45 minutes
 

BSALV

Banned user
Joined
Oct 31, 2010
Messages
1,651
Office Version
  1. 365
  2. 2013
  3. 2007
after 8 hours, there is a break of max 45 minutes
version2
 

HappyChappy

Active Member
Joined
Jan 26, 2013
Messages
325
Office Version
  1. 2019
  2. 2010
  3. 2007
Platform
  1. Windows
just had a look it seems good apart from if you work less than 8hrs ie: 5.45 it should always pay a min of 8 hours pay
also the new overtime calculations are only for monday to friday
all day Saturday is paid at £22ph
all day sunday is paid at £30ph
all day bank holiday is paid at £31ph

many thanks
 

HappyChappy

Active Member
Joined
Jan 26, 2013
Messages
325
Office Version
  1. 2019
  2. 2010
  3. 2007
Platform
  1. Windows
looks the business thank you once again
 

Forum statistics

Threads
1,186,085
Messages
5,955,745
Members
438,214
Latest member
Siledhrel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top