Skip to main content

How to calculate hours worked and minus lunch time in Excel?

Author: Xiaoyang Last Modified: 2020-11-05

As a company employee, we need punch in and punch out every work day, counting the total worked time and minus the lunch time of a day can help to calculate salary according to the time. For example, I have the following daily attendance record which contains log in, log out and lunch start, lunch end times in a worksheet, now, I want to calculate the total hours worked but minus lunch time each day. To finish this job, I can introduce some formulas for you.

doc calculate worked hours 1

Calculate hours worked and minus lunch time with formulas


arrow blue right bubble Calculate hours worked and minus lunch time with formulas

The following formulas can help you to calculate worked time a day based on the time records.

1. Enter this formula: =SUM((C2-B2)+(E2-D2))*24 into a blank cell beside your time record cells, F2, for instance, see screenshot:

Note: In the above formula: C2 is the lunch start time, B2 is the log in time, E2 indicates the log out time and D2 is the lunch end time. You can change the references based on your need.

doc calculate worked hours 2

2. Then drag the fill handle down to the cells that you want to apply this formula, and all the total hours worked excluding the lunch time are calculated at once. See screenshot:

doc calculate worked hours 3

Tips: If there are log in, log out time record and fixed lunch time, (such as one hour and thirty minutes), you can apply this simple formula: =D2-B2-C2 to calculate the worked hours a day. See screenshot:

doc calculate worked hours 4

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

Description


Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
Comments (39)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
I need to count the working time in excel when the finish time is on the next day. Starting time 21:00, finish time at 06:35 next morning.
Which formula can I use for this?
Thanks
This comment was minimized by the moderator on the site
hi, dont know if you'll be able to help me but for my payroll i pay employees by the hours of actual service not by the time they arrive/ leave. i have three categories 1 hr service, 1.5 hr service and 2 hr service. for each category i pay a different amount. i managed to put this data on excell but its counts how many times they completed each service. like 3x the 1 hr and 1x the 1.5 hr on one day or so on. but i also need it to calculate the total hrs they worked. currently it doesnt count the 0.5. any ideas? thanksss!!!
This comment was minimized by the moderator on the site
Hi,

How to Count (D71) CONDITIONALLY FORMATTED RED COLOR CELL only (D40:D70)?

Pls guide...

Thanks
This comment was minimized by the moderator on the site
A2 is my start time at 6pm and B2 is my endtime at 4:00 AM it gives me ###### and format gives me a negative number when its a 10 hour shift. How do I resolve this?
This comment was minimized by the moderator on the site
Hello, Wally,
To solve your problem, please apply the following formula:
=(B2-A2+(B2<A2))*24


Please have a try, hope it can help you!
This comment was minimized by the moderator on the site
Hi there,
I have Cell G9 as Start Time (09:00:00) then F9 as Finish Time (18:00:00) and have a formula for working out the time worked (=IF(F9<E9,F9+1,F9)-E9) but would like to add a formula to calculate If time worked is more that 6 hours, to deduct 00:30:00 minutes for a break and calculate the new time.
Please advise what best formula to use for this?
This comment was minimized by the moderator on the site
Hello,
To solve your problem, please apply the below formula:

=B2-A2-TIME(0,INT((B2-A2)/TIME(6,0,0))*30,0)

B2 is the End time, and A2 is the Start time.
Please try, hope it can help you!
This comment was minimized by the moderator on the site
Добрый день! Подскажите, пожалуйста, у меня есть время прибытия и отбытия, мне нужно ввести формулу сколько на точке пробыл объект и минус 1 час обеда, тоесть если прибыл в 8:00 а отбыл 17:00 итого должно получиться 8 часов
This comment was minimized by the moderator on the site
Hello, Аскар,

To solve your problem, please apply the below formula:=(B2-A2)-1/24
B2 is the End time, and A2 is the Start time.
Please try, hope it can help you!
This comment was minimized by the moderator on the site
Ciao, sto cercano una formula o una macro che mi permetta di calcolare le ore di presenza partendo dalle timbrature ma allineate all'orario di lavoro standard.
Per esempio se ho l'orario di lavoro dalle 8:30 alle 12:15 con pausa pranzo e ripresa alle 13.15 fino alle 17:30 (8 ore di lavoro) ma le timbrature sono ad esempio:
Timbratura di Ingresso ore 8:10 con timbratura uscita pausa alle 12:16. timbratura di ingresso dopo la pausa alle 12:50 timbratura di uscita 17:35.

Sicuramente tu potrai aiutarmi. Grazie in anticipo
This comment was minimized by the moderator on the site
Thanks for the info on this website and for your help!!
I've followed the thread and have made my excelsheet. I use this formula =SUM((C5-B5)+(E5-D5)) to calculate the total working hours per day. I don't get the right info if I use =SUM((C5-B5)+(E5-D5))*24 as has been suggested in this thread. I'm using "Time format' instead of general. Even if I choose general I still get an odd number when I type in 8:00AM start and finish 5:30 PM.

Now I want to have the monthly working hours so I use =SUM(L5:L35), but instead of getting the total hours and minutes I get hours/minutes that aren't correct. For example if the total amount is supposed to be 150 hours and 32minutes I get a whole different number.

Note: I have the format in hh:mm

I hope you could help me out.
This comment was minimized by the moderator on the site
Hi, NG,
I'm sorry for that this formula can't help you, You'd better to upload your Excel file here if you don't mind, so that we can check where the problem is?
Thank you!
This comment was minimized by the moderator on the site
Thanks for the reply. How/where do I upload my file? I don't see an attachment possibility.
This comment was minimized by the moderator on the site
Hello, NG,
If there is no "Upload Attachment" box, you should register first, and then the "Upload Attachment" option will be appeared.
To register, please go to the top of the article, and click Resgister button to start.
https://www.extendoffice.com/images/stories/comments/comment-skyyang/doc-register.png
I'm sorry for the inconvenience.
This comment was minimized by the moderator on the site
Začátek pracovní doby i konec se vždy liší, jak prosím automaticky odečíst 30 minut bez zadávání začátku a konce přestávky?

Děkuji
This comment was minimized by the moderator on the site
非常实用!很感激你的帮助!请教你如何计算公式得到员工的总工资总和。
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations