what a headache please help

Originally Posted by Crasher

hi all please let me say sorry because this may take some time to explain. please note that you will have to download my worksheet so you can see what it is that im on about below.

here goes.....

in c5 i have the option of selecting BankHols which in turn generates a figure in L5 (which is 10*8.9665) great!!) which does what it is wanted..however this is where it now gets TRICKY..........

this is the formula that is used at the moment:-

=IF(C5="bankhols",10*L4,"") L4 represents 8.9665 if i do not work.

this is where you will need to see the sheet

if i do work i need the above formula to change to 8*L4 plus the hours i work but the hours i work needs to be calculated at double time.

example 8*L4=71.73
plus if i do 10hours work i would need to add 9.25*17.933=165.88 (the reason for the 9.25 is because i have to deduct 3/4hr for a break).

so in total the figure that need to show in L5 is 237.61 if i do not work then it should show 89.66

--------------------------------------

the other thing i cant work out is if i do work then i need I5 to remain as it is without calculating anything. but if it is not a bank holiday then it is just to do its job.

this is the formula

=G5*$I$4

HI,

Noted that G3 should be Overtime, not Over Time

after a date is entered into B5, then B6 should be =B5+1 which can formula fill downwards
A5 should be =Text(B5,"dddd") formula fill downwards

then future months will be easier to setup.

what happens if someone is sick for a half-day?

A bank holiday is a bank holiday, whether a person works or not should be immaterial, it's still a bank holiday, however I can understand that you want to treat worked hours differently.

If 'basic hours' were the non-overtime portion of E - D (ie
=If(Weekday(B5,2)<6, then calculate F as E-D to either a maximum of 8 (F$4) and the remainder in G (less the 3/4 if required) -OR- 8 hours in D if C5 = sick or bankhols etc and if required, any remainder in G

With your hours calculated you can then use =If(F5 to determine whether you put figures in H I J K or L
When you decide to increase the table at A53:A58 there will be less problems involved.

This approach avoids the problem stated.

Does this help you?

---

not really...you just confused the heck out of me sorry

this worksheet is designed for truck drivers like myself so there is no half day sick.....

with what you are proposing and what im after how would you suggest the formula would look like cos im not really that great at doing complex formulas.

Originally Posted by Crasher

not really...you just confused the heck out of me sorry

this worksheet is designed for truck drivers like myself so there is no half day sick.....

with what you are proposing and what im after how would you suggest the formula would look like cos im not really that great at doing complex formulas.

Hi,

didn't mean to confuse you, I have adjusted column A and B so that the first date in B5 will reset all following dates, but, of course, B5 must be a Monday date.

You need a way to specify the 'Double hours' worked on a Bankhol, so I adapted the 2* already there, with white font.

I seem to have missed a small point as the amount does not total 237.61, but 255.55 - I will check this further to detect the error.


A time in D5 indicates working on a Bankhol, otherwise the default 10 is used to give 89.67 as before.

Does this help in the right direction you are looking to go?

Amended - in H5 and I5, the formula is:H5

=IF(C5="Bankhols","",SUM(F5*$H$4))

in I5

=IF(OR(C5="Bankhols",G5=""),"",G5*$I$4)

---
further amended, I think your 237.61 should be 255.55 being 165.88 + 89.67

Adjustment required to L5 formula for non-hols days

=IF(C5<>"bankhols","",(10*L4)+IF(J5="",0,J5))

as per the attached.

---
I can understand why you said 'headache'

the formula in L5 is 95% there. however when i work the figure seems to still *L4 by 10 when i work when it should *8.

it should only times 10 when i do not work.



BTW J5 is spot on!!!!

Originally Posted by Crasher

the formula in L5 is 95% there. however when i work the figure seems to still *L4 by 10 when i work when it should *8.

it should only times 10 when i do not work.



BTW J5 is spot on!!!!

ok - try

=IF(C5<>"bankhols","",IF(J5="",10*L4,8*L4+J5))

let me know how that is
---

Originally Posted by Crasher

could not be any more perfect......

many thanks i really mean it

Good to see that it's working for you, and thanks for the response.

----