Formula Errors

Excel Weekly Roster Formula for Times and Decimal Hours

Aamir Naveed Akram Sep 27, 2026 869 views

Question details

The user needs a formula to create a weekly roster that accepts four-digit time entries, deducts daily lunch breaks, and computes the total weekly hours as a decimal.

Product: Excel
Device & OS: not provided
Scenario: Creating an efficient timesheet where employees or managers can input time rapidly without typing colons (e.g., 0800), and have the spreadsheet automatically convert it to standard time and calculate payable decimal hours.
Observed behavior: The user is looking for the correct combination of formulas and formatting to bypass manual colon entry, subtract break times accurately, and aggregate the total into decimal format.

Before you start

Ensure that your four-digit time entries (like 0800 or 1700) are formatted as 'General' or 'Number' rather than 'Text' so the calculation formulas can process them properly.

Solution 1Recommended

Use INT, MOD, and TIME Formulas for Time Conversion

Use helper columns to separate the hours and minutes from your four-digit number, convert them into standard time values, and calculate the daily and weekly totals.

By utilizing the INT and MOD functions, you can extract the hour and minute components from a raw four-digit number. Wrapping these inside the TIME function converts the raw numbers into a readable time format that Excel can use for mathematical calculations.

Set up your raw data columns

Enter your four-digit start times in column B (e.g., cell B2) and your four-digit end times in column C (e.g., cell C2).

Convert the start time

Create a helper column for the actual Start Time. In cell D2, enter the formula =TIME(INT(B2/100),MOD(B2,100),0). This turns a number like 0830 into a valid 08:30 AM time value.

Convert the end time

Create another helper column for the actual End Time. In cell E2, enter the formula =TIME(INT(C2/100),MOD(C2,100),0).

Subtract the lunch break to get daily hours

In cell F2 (Daily Hours), subtract the start time and your standardized lunch break (e.g., 30 minutes) from the end time using the formula =E2-D2-TIME(0,30,0).

Calculate and format weekly decimal hours

To get the weekly total, use =SUM(F2:F8) at the bottom of your Daily Hours column. Format this total cell as a Number to display the time in a decimal hours format.

Use INT, MOD, and TIME Formulas for Time Conversion

Converting Time to True Decimal Hours: Because spreadsheets store time as a fraction of a 24-hour day, standard time summation might look like a fraction. If you want true decimal hours (e.g., 40.5 hours), you should multiply your sum by 24 using =(SUM(F2:F8))*24 and format the cell as a Standard Number.

Efficient Time Tracking with WPS

Build Automated Timesheets with WPS Spreadsheet

WPS Spreadsheet fully supports advanced time tracking and calculation formulas. You can seamlessly convert times, calculate weekly rosters, subtract breaks, and convert totals to decimal hours using the exact same formulas as Excel.

1. Open a New Roster: Launch WPS Spreadsheet and open a blank workbook or select a Timesheet template.
2. Input Your Formulas: Type your raw four-digit times and apply the TIME, INT, and MOD formulas just as you would in Excel.
3. Calculate Totals: Use the SUM function to aggregate daily hours, multiply by 24, and easily change the cell format to Number for perfect decimal hours.

100% compatible with Microsoft Excel formulas, including TIME, INT, and MOD.Completely free to use with a lightweight, user-friendly interface.Provides a rich library of built-in templates for timesheets and employee rosters.Flawless compatibility with standard Excel (.xlsx) file formats.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my time conversion formula returning a #VALUE! error?

This typically occurs if your four-digit time entry is formatted as text with hidden spaces, or contains non-numeric characters. Check the cells containing your four-digit times and ensure their formatting is set to 'General' or 'Number'.

How can I subtract a different lunch break duration, like 45 minutes?

You can easily modify the TIME function in your daily calculation. For a 45-minute break, change TIME(0,30,0) to TIME(0,45,0) in your equation. The format for the TIME function is TIME(hours, minutes, seconds).

Can I enter standard times with colons instead of four-digit numbers?

Yes. If you prefer to enter standard times (e.g., 08:30), you can skip the INT and MOD conversion formulas entirely. Simply enter the times with colons and subtract the start time from the end time directly.

Excel & Data Problems