help with complicated formulas

F

flyinghigher2011

New member
Joined
Jul 29, 2013
Messages
7
Reaction score
0
Points
0
Hi everyone :D

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
 

Attachments

  • working progress.xlsm
    130.5 KB · Views: 19
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.
 

Attachments

  • work progress-A.xlsm
    127.3 KB · Views: 17
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
 
Well, for a pop-up type of alert, you will have to work in VB. Unfortunately, my skills there are rough.
 
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) ?
 
You must log in or register to reply here.
Back
Top