Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

1. I agreed to these rules
Join Date
2nd August 2005
Posts
1

## 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. ## 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. Highly Valued Member
Join Date
26th January 2003
Location
UK
Posts
4,635

## Re: Payroll Formula

Moved to the Excel / VBA forum

jiuk

Excel Video Tutorials / Excel Dashboards Reports

4. Super Moderator
Join Date
26th January 2003
Location
Derbyshire,UK
Posts
19,719

## Re: Payroll Formula

There's a simple Timesheet here : free examples

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

#### Posting Permissions

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