Calculation Issues

How to Calculate Working Hours Minus Lunch Break in Excel

Maira Mehtab Sep 22, 2026 869 views

Question details

Calculate the total time worked during a daily shift (e.g., 8:00 AM to 5:00 PM) while automatically deducting a specific duration for a lunch break (e.g., 45 minutes).

Product: Microsoft Excel
Device & OS: not provided
Scenario: Tracking employee work hours or personal timesheets.
Observed behavior: Needs a formula to subtract a specific minute value from a calculated time duration to get accurate net working hours.

Before you start

Ensure your start and end time cells are properly formatted as 'Time' (e.g., h:mm AM/PM) before applying the formula so the spreadsheet can correctly calculate the duration.

Solution 1Recommended

Use the TIME Function to Subtract Lunch Breaks

The most straightforward way to deduct a fixed lunch break duration from a daily shift is by using basic subtraction combined with the TIME function.

Excel stores dates and times as serial numbers, which means you can mathematically subtract them. By using the TIME function, you can specify exactly how many hours, minutes, and seconds you want to deduct without worrying about decimal conversions.

Enter the start and end times

Input the shift start time in one cell (e.g., type 8:00 AM in A2) and the end time in another (e.g., type 5:00 PM in B2).

Apply the time subtraction formula

In cell C2, type the formula =B2-A2-TIME(0,45,0) and press Enter. The TIME function represents hours, minutes, and seconds, so TIME(0,45,0) accurately deducts 45 minutes.

Format the result cell

Right-click cell C2, select 'Format Cells', choose the 'Time' category, and pick a format like 'h:mm' to display the result as hours and minutes (e.g., 8:15).

Handling Overnight Shifts: If your work shift crosses midnight, use the formula =(B2-A2+(B2<A2))-TIME(0,45,0) to ensure the duration calculates correctly before subtracting the lunch break.

WPS Spreadsheet Solution

Easily Track and Calculate Timesheets with WPS Spreadsheet

WPS Spreadsheet perfectly supports all standard Excel time functions, making it incredibly easy to track work hours, manage employee timesheets, and deduct lunch breaks accurately.

1. Open a new spreadsheet: Launch WPS Office and open a new blank WPS Spreadsheet.
2. Input your time data: Enter your Shift Start Time and Shift End Time in two adjacent columns.
3. Enter the calculation formula: Type the =B2-A2-TIME(0,45,0) formula into the total hours column to instantly calculate net working time.
4. Format the output: Select the result cells, go to the Home tab, click the Number Format dropdown, and select 'Time' to view your net hours seamlessly.

100% compatible with Microsoft Excel time formulas and cell formattingFree and lightweight alternative for daily data analysis tasksBuilt-in templates for timesheets, scheduling, and payrollSeamless user interface familiar to Excel users

microsoft office alternative - wps office

Frequently Asked Questions

What if the lunch break is a full hour instead of 45 minutes?

Modify the TIME function within your formula to TIME(1,0,0). The first parameter is hours, so this will deduct exactly one hour from your total worked time.

Why does my result show as a decimal number instead of hours and minutes?

Spreadsheet software stores time as fractions of a 24-hour day. To see hours and minutes, select the result cell, press Ctrl+1 to open Format Cells, and apply a 'Time' format.

How can I subtract lunch breaks when start and end lunch times are recorded separately?

If you track Punch In (A2), Lunch Out (B2), Lunch In (C2), and Punch Out (D2), you do not need the TIME function. Simply use the formula =(B2-A2)+(D2-C2) to sum the morning and afternoon work periods.

Why am I getting a ###### error in my formula result?

This error usually occurs when a time calculation results in a negative value, which standard time formats cannot display. Ensure the end time is greater than the start time, and verify that the lunch deduction does not exceed the total shift duration.

Excel & Data Problems