# Thread: help with complicated formulas

1. ## help with complicated formulas

Hi everyone

i need some help with my worksheet, ive scoured many youtube videos and other sites to help but to no avail. i have attached my worksheet for people to take a look at to try and get a grasp of what im on about.

i need some formulas to work out time worked and it needs to incorperate overtime and if someone has a sick day, holiday or just a day off. i need this to be one formula, i also have a section that need to calc working day eg 13 or 15 so if someone works >=13hrs then the box needs to show 13hrs and if 13hrs and 01min it need to show 15. also i need a warning of some sort either by changing the cell colour to red or have pop up to day that if they use a 15hr then they have 2 more 15's left and if they use another the needs to say 1 more left and if last one is used then zero left. this need to calc per weeks. similarly for the driving hours. a driver may extend only twice per week.

could someone help as im getting desperate now.

many thanks
Paul

2. Well, I am little confused by your needs. But, reference the attached spreadsheet. First off, I had to convert the OT Worked formula to convert to decimals. That helped the overall maths.

The Working Day 13 or 15 is just a simple IF formula: =IF(I9>13, 15, 13)

As for the alert system, there are several ways to do it. In the example I put in, the Working Day column header is fitted with a conditional formula that counts the number of 15's and changes the header color based upon the result (green, yellow, red).

Hope that helps.

3. thank you for your help however now sure on the light system though i need something more in your face like a popup please and its as great start just need to have the holiday sick and off added in to the formula

4. Well, for a pop-up type of alert, you will have to work in VB. Unfortunately, my skills there are rough.

5. Hello
What would you want to pay an employee who was either off, sick or on holiday (e.g. half pay, full pay, or no pay if off without reason) ?

6. Originally Posted by Hercules1946
Hello
What would you want to pay an employee who was either off, sick or on holiday (e.g. half pay, full pay, or no pay if off without reason) ?
it would be no pay for sick, 8 hours if holiday, and no pay for off

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•