Results 1 to 7 of 7

Thread: help with complicated formulas

  1. #1

    help with complicated formulas

    Register for a FREE account, and/
    or Log in to avoid these ads!

    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
    Attached Files Attached Files

  2. #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.
    Attached Files Attached Files

  3. #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. #4
    Admin Alumnus Pecoflyer's Avatar
    Join Date
    Oct 2011
    Brussels Belgium
    Excel Version
    2010 on Xubuntu

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

  6. #6
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    York, England
    Excel Version
    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) ?

  7. #7
    Quote Originally Posted by Hercules1946 View Post
    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