Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

Results 1 to 4 of 4

Thread: Payroll Formula

  1. #1
    Join Date
    2nd August 2005

    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

    Excel Video Tutorials / Excel Dashboards Reports

  2. #2
    Join Date
    9th June 2004

    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

    Excel Video Tutorials / Excel Dashboards Reports

  3. #3
    Join Date
    26th January 2003

    Re: Payroll Formula

    Moved to the Excel / VBA forum


    Excel Video Tutorials / Excel Dashboards Reports

  4. #4
    Join Date
    26th January 2003

    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.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Payroll Formulas
    By LaurenSA in forum Excel General
    Replies: 1
    Last Post: November 2nd, 2006, 12:58
  2. payroll computation
    By aboiy-jrd in forum Excel General
    Replies: 10
    Last Post: April 19th, 2005, 21:34
  3. Payroll Formula
    By sosassy29 in forum Excel General
    Replies: 3
    Last Post: February 19th, 2005, 05:42
  4. Payroll Formulas
    By Jerry Hartsell in forum Excel General
    Replies: 1
    Last Post: September 20th, 2004, 00:20


Posting Permissions

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