help with complicated formulas

**flyinghigher2011** · 2013-07-29, 06:26 PM

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

**dond427** · 2013-07-29, 09:55 PM

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.

**flyinghigher2011** · 2013-07-29, 11:43 PM

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