Calculation Issues

How to Subtract a 30-Minute Lunch Break from Excel Working Hours

Kushani Nimanthika Sep 27, 2026 869 views

Question details

The user needs to calculate total working hours by subtracting a 30-minute lunch break from start and end times, while resolving #VALUE! errors caused by times entered as text format.

Subtract a 30-Minute Lunch Break from Excel Working Hours

Product: Excel
Device & OS: not provided
Scenario: Calculating weekly hours worked and total pay for timesheets or payroll, requiring accurate deductions for break periods.
Observed behavior: The subtraction formula returns a #VALUE! error because the start and end times are formatted as non-standard text (e.g., '10.50am') instead of valid Excel time values.

Before you start

Check your spreadsheet to see if your start and end times are stored as standard Excel Time values (using colons, like 10:50 AM) or as text strings (using periods, like 10.50am). This determines which calculation method you should use.

Solution 1Recommended

Calculate Working Hours Using Standard Time Values

Use this method if your start and end times are already recognized by Excel as valid time formats.

Excel handles dates and times as serial numbers. When time cells are formatted correctly, you can perform basic mathematical operations on them using the TIME function to deduct specific durations.

Select the target cell

Click on the empty cell where you want to display the total working hours for the day.

Enter the deduction formula

Type the formula =EndTime-StartTime-TIME(0,30,0) (replace EndTime and StartTime with your actual cell references, such as =B2-A2-TIME(0,30,0)). The TIME(0,30,0) function deducts exactly zero hours, 30 minutes, and zero seconds.

Format the result cell

Right-click the result cell and select 'Format Cells'. Go to the 'Custom' category and enter [h]:mm in the Type box. This formatting ensures that hours over 24 are displayed correctly without resetting.

Calculate Working Hours Using Standard Time Values

Formatting Tip: If you plan to calculate weekly totals that exceed 24 hours (e.g., 74 hours), applying the [h]:mm format is strictly required.

Solution 2

Convert Text-Formatted Times to Calculate Hours

Use this formula if your time data is stored as irregular text (e.g., '10.50am') and causes #VALUE! errors.

WPS Spreadsheet Productivity

Calculate Timesheets Easily with WPS Spreadsheet

WPS Spreadsheet provides seamless time and date calculation functions identical to Excel. Easily track working hours, format time data, and handle complex payroll calculations in a highly compatible environment.

1. Open your timesheet: Launch WPS Spreadsheet and open your daily or weekly timesheet document.
2. Apply the time formula: Use the =EndTime-StartTime-TIME(0,30,0) formula to automatically deduct lunch breaks from your shifts.
3. Set custom cell formatting: Right-click the cell, choose 'Format Cells', and apply the [h]:mm custom format to display cumulative working hours accurately.

Highly compatible with Microsoft Excel formats (.xlsx and .xls)Supports advanced time formatting and TIME functionsIncludes smart data formatting to easily fix text-based time errorsFree and lightweight alternative for daily office tasks

microsoft office alternative - wps office

Frequently Asked Questions

Why do I get a #VALUE! error when subtracting working hours?

This happens when the spreadsheet reads your time entries as plain text strings rather than real time values. Ensure your times are formatted properly using colons (e.g., 10:50 AM) instead of periods (e.g., 10.50am).

How do I multiply the total hours worked by an hourly wage?

Spreadsheets store time as a fraction of a 24-hour day. To accurately calculate wages, multiply your total time value by 24, and then multiply by the hourly rate (for example, =(TotalTime*24)*30).

What does the custom format [h]:mm do?

The square brackets around the 'h' tell the spreadsheet to display cumulative, total hours. Without the brackets, standard time formatting rolls over after 24 hours, causing a 25-hour duration to display simply as 1 hour.

How do I deduct a 1-hour lunch break instead of 30 minutes?

You can modify the TIME function's parameters. Simply replace TIME(0,30,0) with TIME(1,0,0) in your formula to subtract exactly one hour from the worked duration.

Excel & Data Problems