Working time minus lunch Results

Aloha everyone!

I'm quite new to excel and was hoping someone here can help guide me into the right direction on creating a function that will tell me the total hours that were worked based on times minus lunch break and convert it into decimal format for payroll purposes.

I included a zip file to give you an idea and the type of decimal format I'm trying to achieve.

Appreciate any advice in this matter

I am working on a spreadsheet to calculate work schedules minus lunch hours and I am trying to total them for the week as well as for the day.
I have the formula to calculate the daily time =U8-T8-(TIME(0,30,0)), and to calculate the weekly time I am summing the daily time. However, my sum does not add correctly and my total daily does not add correctly.

I will attach the spreadsheet so you can see what I am doing. Row 11 is calculating correctly, but I have a time for each day of the week, however row 8 is not correct, I do not have a time for every day. Also my total in cell V27 does not calculate correctly.

I think I may be overthinking this one, but it is making me crazy and I need to have it done by the end of this week. Can anyone help?

nick 2.xls

exel shema hjälp.xlsx

please open attach to read and see my problem..

Hello, I am trying to put together an excel program to calculate total number of work hours, but with some restrictions such as rounding or taking out some time for being late. for example: if work from 6:00 PM to 2:30 AM of the next day, I'll be working 8 hours (minus 30min lunch). However, if I punched in at 6:01 PM they took away .25 minutes (15 min) so I only get paid 7.75 hours. can anyone tell me how can I express that on excel? what formula should I use? I want to be able to calculate that for any given time.

Thank you

QUESTION: I have a spreadsheet including all of my in and out times for work, leave, and lunch. If I leave in the morning for an appointment that runs through my lunch period, what formula can I use to calculate the time away from the office MINUS the lunch period or said another way, how can I get Excel to only count leave time that does not fall within the range representing my 45 minute lunch time?

I often have medical appointments during the day that I use either sick or annual leave for. I try to make my appointments overlap with my lunch time in order to reduce the amount of leave I must claim. My employer counts time in whole hour increments only, so we must keep our own running totals for partial hours used until it equals a whole hour. I'm able to handle the increments by rounding down to the whole hour interger and adding the remainder to a running total. Other details:

For example I arrive for work at 7:00 AM, leave for an appointment at 10:30 AM (begin sick leave calculation), my lunch start is 11:00 AM and lunch end is 11:45 AM, and I return from my appointment at 1:00 PM. I want B11 (below) to show 1hr 45 min or 1.75 instead of 2 hrs 30 min or 2.50.

-All time cells are formatted with h:mm AM/PM
-"Number hours used" cells are formatted as numbers with 2 decimal places
-"Number hours claimed" cells are formatted as number with no decimal places
-Current formulas in speadsheet are show in brackets [ ]

B2 - Arrival Time [user entry]
B3 - Lunch Start [user entry]
B4 - Lunch End [=SUM(B3+0.0315)]
B5 - Sick Leave (S/L) Start [user entry or empty]
B6 - S/L End [user entry or empty]
B7 - Annual Leave (A/L) [user entry or empty]
B8 - A/L End [user entry or empty]
B9 - Departure [user entry]
B10 - Number hours worked [=SUM((B9-B2)*24)-0.75
B11 - Number S/L hours used ?????
B12 - Number of A/L hours used ?????
B13 - Number of work hours claimed [=INT(B10)-(B14+B15)
B14 - Number S/L hours claimed [=INT (B11)]
B15 - Number A/L hous claimed [=INT(B12)]

I truly appreciate any help. I don't think the answer is difficult, just beyond my mental reach.

How do I see if the time worked (start and finish times) is in a span of defined hours.
For instance how do I check that hours worked or part hours worked are within or not within the range 18:00 to 00:00 on day 1 and 00:00 to 06:00 on day two?

I have used the formula below where F5 is the start, F6 finish times for lunch, F8 start time after lunch F9 Finish time and F11 & F12 start and finish times.

start_1 is defined as 18:00, Finish_1 as 00:00 for day one, Start_2 as 00:00 and Finish_2 as 06:00 for day two

=IF(OR(AND(F5>=Start_1,F5<=Finish_1,F5<>""),AND(F5>=Start_2,F5<=Finish_2,F5<>""),AND(F6>=Start_1,F6< =Finish_1,F6<>""),AND(F6>=Start_2,F6<=Finish_2,F6<>""),AND(F8>=Start_1,F8<=Finish_1,F8<>""),AND(F8>= Start_2,F8<=Finish_2,F8<>""),AND(F9>=Start_1,F9<=Finish_1,F9<>""),AND(F9>=Start_2,F9<=Finish_2,F9<>" "),AND(F11>=Start_1,F11<=Finish_1,F11<>""),AND(F11>=Start_2,F11<=Finish_2,F11<>""),AND(F12>=Start_1, F12<=Finish_1,F12<>""),AND(F12>=Start_2,F12<=Finish_2,F12<>"")),F15,"")

The problem is that that formula is very cumbersome and not good programming and I would have to train useers to use 24:00 instead of 00:00 but then you canot use subtraction to get the times of hours worked; 03:00 minius 24:00 does not work where as 03:00 minus 00:00 = 3 hours

I am pretty new to Excel and currently have two problems that I need help with. I have linked several cells from worksheet 1 onto worksheet 2 but the background colors from worksheet 1 did not come over with the data. Is there any way to bring the format and background colors over from one sheet to another?

Also, I am doing a payroll worksheet that subtracts In time from Out time minus lunch to get number of hours worked. I am using military time so the subtraction will work but I am calculating hrs worked based on tenths of an hour instead of sixths of an hour in order to get the correct hours worked. Example: It should be: 05:30am to 1:30pm minus .30 for lunch = 7.50 hrs worked.
Instead I am using the following calculation 13.50-05.50-.50 to get 7.50. I am sure there is a better way to do this but I don't know how. Any help will be appreciated.

Hi below is a sample of how my worksheet look like.

Starting from B1 cell

Start date Start time End date End time
8/5/2010 9:00 8/5/2010 14:00
8/7/2010 9:00 8/9/2010 15:00

Working Day Mon to Fri 0830 to 1730 Lunch hr 1
Working Day Sat 0830 to 1230 Lunch hr 0

Start date Day 1 End date Day 2 Elapse Time Total Hrs
8/5/10 9:00 Thu 8/5/10 14:00 Fri 4:00
8/7/10 9:00 Sat 8/9/10 15:00 Mon 2.21

The formula that i using on Elapse Time are


Which is the right answer.


But there is an scenario where employee tend to end work before lunch hour. So in this case, is there a formula to work with above formula that indicate different lunch hour whereby if employee end work before lunch or start work after lunch hour. The minus 1 hour will not be calculated.


How do I get networkday to calculate through Sat to Mon and exclude Sun? Given Sat = 4 hours, Mon = 9 hours of business hours.


In my time report I have Start Time, Finish Time, Lunch Time, Total and Overtime Hours. Because I work nights, I am using the MOD command to calculate the worked hours which boils down to Start Time - Finish Time - Lunch Time.
The problem I have is that we are paid extra time from 8pm to 6 am. Is there an easy way to get the spreadsheet to calculate the hours between the Start Time minus the Lunch Time upto 06:00?
Everything I have tried just dosnt work.
I have attached a copy of of my Time Report so you can see what I am talking about.
Thanks in advance

Hi All

I'm struggling with a formula.

I am creating a worksheet where employees schedule work throughout the day
eg task 1 will be start time 9am, finish time 11am. The result is 2 hours to do the task.

The problem i'm having is that I need to do a calculation that if a task includes lunch then the result takes an hour away from the task time.
eg task 2 will be start time 12pm, finish time 3pm, excel displays 3 hours, i need it to say 2 hours because 1-2pm is contractual lunch.

I can't just do a day formula that says minus 1 from total as this is task orientated and will vary depending on start and finish times of each task.

I hope someone can help.
PS, it needs to be a formula and not vba as we have a problem with macro's if multiple people use the worksheet.

Hi - I need to be able to calculate the hours and minutes worked daily by employee.

The shift may begin before but not end until after midnight. There are instances where an employee will need or be asked to take an "early out" so that they will not work a full 10-hour shift.

If the clockout time minus the clockin time is 6 hours or more, 30 minutes must be deducted for a lunch break.

I managed to get the "total" time calculated (before lunch break allowance) but could not get the lunch break 30 minutes to work. In each case, the 30 minutes was deducted whether accurate or not.

Your help is appreciated.

I am new here and i am really looking for help. My instructor gave us assignment of how to create a formular in hours field to calculate total hours work minus the lunch break.

It has

Start Lunch out Lunch in End
11.30 am 11.45 am 12.30 pm 1.14pm

Please help me how do i calculate the total hours work minus lunch break.

Thank you

I'm working on a timesheet where as the user will only type the hours by the hundreds e.g. 145 = 1:45
and the cell is formatted as 0":"00" AM" or 0":"00" PM" the reason for this is for fast input. And in there is also a cell that is in decimal for how long the employee took for lunch break say 1.30 = 1hr 30 min.
I need a formula that will calculate the hour from Time in & Time out minus Lunch Break in 8hr/day.

Time In (AM)......8:00 Am
Time out (PM).....1:45 PM

Please help! thanks in advance

Help, I need to calculate hours owed.

Hi All,
I have a sheet that tracks hours worked and I need to calculate hours still owed.

Cell "C4 is "Time In", D4 is "Time Out", E4 is the lunch break, defaults to :30 min, F4 is the total hours worked (C4,D4,E4) and in H4, I want to calculate the total hours minus 40 hours. However I tried "=(40)-F4" and am getting strange results for example 6 total hours returns an hours owed as 39.75 in stead of the correct value of 34.

Can anyone suggest a formula? I did a search and found a formula that is almost, but not quite right.


Im currently trying to export data, from a constantly updated Moss held database, to an Excel spread sheet for view with some extra formating.

My issue is that when i chose web query to retreive the data all feilds are correct minus the time/date.


Persons name | 1/3/2010 5:02 | out to lunch |

Now on the site the time reads as 1/3/2010 5:30 wich is the correct time

If i choose to export the data to an Iqy file and import it into the spread sheet i end up with a log in and password prompt every 10min or so. The data however is correct.

My methods of trouble shooting so far have been to enable the check box to disable excels auto date/time formating, and to disable web redirect (which works, while ruining the formating of data. It add press shift alt something to every line of the persons name)

if anyone has any advice it would be highly appreceated.

I have some freelancers that have submitted timesheets that don't match the
hours I have them worked. I can do it manually but is there a formula for the

Time In minus Lunch, that total with Time Out equals Total time worked. Then
the difference of Total time worked from Billed. Thanks!

Worker Time In Lunch Time Out Total Billed Diff
Bob 12:30 0:00 14:30 2:00 2.00 0.00
Jeff 12:30 0:00 14:30 2:00 2.00 0.00
Bob 10:00 0.5 17:00 6.50 10.00 3.50
Jeff 10:00 0.5 17:00 6.50 10.00 3.50
Bob 10:30 0.5 17:00 6.00 8.00 2.00
Jeff 10:30 0.5 17:00 6.00 8.00 2.00

I'm working on a project for work... I have created an excel worksheet to show team mate name, start time, end time, but what i want is for in column e is the time that we have them VTO so if they leave at 9 at night and their shift does not end till 3.30 that it would come up and show that i saved 5 hours of time. (5 hours 30 mins, minus 30 min lunch at 930)... can anyone help????


My name is Scott, and i am working on a scheduling system that costs itsef automatically. The aim is the when I draw up schedules for employees, i input the hour they start and the hour they finish. i.e.
1 * M o n d a y *
2 *Start * Finish *
3 Employee A * 0900 * 1300 *
From the start and finish times I work out the number of hours worked per day (minus 1 hour for lunch if working over 8 hours). Using the following equation =IF(B3-A3>800,B3-A3-100,B3-A3). (I have using numbering instead of time as I am not sure how to convert time in numbers for costings). My problem is converting quarters in time to quarters in numbers for costings. ie schedules 0915 - 1330, needs to be 0925 - 1350, as the schedules would be misleading if written the send way.

So if you can help, or need more info, please leave a message and i can email the worksheets over.

Thank you

Scott Pattison

Hello Forum,

I am hoping that you can help me out.

I am trying to build a timesheet for work.
I have the employees start time in cell A1 and their end time in cell A2. In cell A3 I have their hours, which the formula I have is =sum(A2-A1) to find out the hours worked (using the 24hr clock system).

My issue is this - I need to create an IF statement which will deduct a half hour lunch if their start or end time is 12:00:00 PM.

Johnny works from 8am to 5pm.
In cell A1 entered is 08:00:00 and in cell A2 it says 17:00:00. I need the cell that calculates the hours (A3) to minus the half hour lunch at noon only IF noon is punched in.

Sometimes they work on different projects throughout the day and I need to tally that.


Johnny starts at 8am and works until 10am. The formula I have now will easily calculate 2 hrs worked. Johnny starts working again at 10:30am and works until 1pm that afternoon. I now need an if statement in there to deduct a half hour for lunch.

Any ideas anyone??
Help is greatly appreciated!

I'm trying to create a spreadsheet to track misc. work related stuff.
As part of the sheet, I would like to calculate break information Like:
What time I have to be back at my desk based on what time I leave for break, and total hours worked w/o counting my breaks.

So, say I clock out for break @ 5:30pm. I would like to input what time I leave for break in cell B, and have C show what time I would need to return.
Ex: 5:30pm + 15mins, means I need to be back @ 5:45pm.

My breaks are 15 minutes, and my lunch is 1 hour. This is kind of how I want it set up:

1 ___________________Break____Return @
2 Start_____________3:30 PM_____n/a
3 1st break (15min)___5:30PM___5:45PM
4 lunch (1hr)_________7:30PM___8:00PM
5 2nd break (15min)__10:30PM__10:45PM
6 End of shift:_______12:00AM_____n/a
7 Total Hours:_______ 8 Hours

Is this possible?
I would need formulas for cells C3, C4, and C5 to show when to be back.
...and for B7, which I would like to calculate my total hours that I worked, minus my breaks.

I know I can calculate what time I need to return in my head, but I would like to track everything in this sheet, and would like it to calculate when I should return vs actually figuring it, and then just typing it into the cell.

I've been trying to figure this out for a few hours, doing various searches, looking at premade templates, and still can't figure it out.
Any help would be GREATLY appreciated.

(I had to put "___" to keep everything aligned as I post it here. It scrunched it together where I originally put spaces. I'm using "___" to try to make this post clear as to how I plan on setting this up. I don't plan on putting "___" in my sheet.)