No announcement yet.

Payroll Formula

  • Filter
  • Time
  • Show
Clear All
new posts

  • Payroll Formula

    I am trying to put a formula together that will add hours & subtract out lunch hours on a daily basis. The formula for weeks end is easy, it is the daily one that is hard. The employees would put a time in at 8:30, lunch out at 1:30, lunch in at 2:30, & then out for the day at 5:45. Since the lunch is past 12, the only way I could get this to work was by adding am's & pm's. Can anyone show me a way to do this without having to type am & pm. The reason being is because I have to submit this paper to my finance department & the paper is maxed out at 8.5 x 14 landscape format. If I put the am & pm's in, it pushes it way over the print line on the right. Please help. Thanks in advance.
    Edit: I don't want to put in Military time either

  • #2
    Re: Payroll Formula

    If you don't want to use AM or PM or a 24 hour clock you will have to introduce a calculation to compensate for the difference.

    I suggest the following -

    Format all time cells as HH:MM

    A1 Start 8:30
    A2 Finish 1:30
    A3 Lunch 1:30
    A4 Lunch 2:30
    A5 Start 2:30
    A6 Finish 5:45
    A7 12:00 Will compensate for not using am/pm or 24 hour clock
    A8 =+A7-A1+A2+A6-A5 = Hours Worked
    A9 =+A4-A3 = Lunch

    Hope this helps


    • #3
      Re: Payroll Formula

      Moved to the Excel / VBA forum



      • #4
        Re: Payroll Formula

        There's a simple Timesheet here : free examples
        Hope that Helps


        New users should read the Forum Rules before posting

        For free Excel tools & articles visit my web site

        If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

        RoyUK's Web Site

        royUK's Database Form

        Where to paste code from the Forum

        About me.