Formula Errors

How to Compare Cumulative Time Values Above 70 Hours in Excel

Bushra Parveen Oct 20, 2026 868 views

Question details

The user needs an Excel formula to accurately compare cumulative time durations against 70-hour and 75-hour thresholds without the time resetting to clock hours.

Excel Formula for Comparing Time Values Above 70 or 75 Hours

Product: Excel
Device & OS: not provided
Scenario: Calculating and categorizing total cumulative work hours or durations that exceed the standard 24-hour limit.
Observed behavior: Using standard time functions like TIMEVALUE for hours over 24 (e.g., 75:00) incorrectly returns the remainder (3:00) instead of evaluating the full cumulative duration.

Before you start

Ensure your time data is formatted correctly using the custom format [h]:mm so that Excel displays cumulative hours rather than a standard 24-hour clock.

Solution 1Recommended

Use Day-Fraction Comparisons with the IF Function

Convert your target hours into Excel's native day-fraction format by dividing by 24, allowing for accurate logical comparisons.

Excel inherently stores dates and times as serial numbers, where one full day equals 1. Consequently, hours are calculated as fractions of a day (1 day = 24 hours). Therefore, a duration of 75 hours is mathematically represented as 75/24.

The TIMEVALUE function evaluates standard clock time and ignores days, meaning a formula like TIMEVALUE("75:00") is evaluated as 3:00 AM (75 hours minus three full 24-hour days). Using day fractions solves this completely.

Select the target cell

Click on the cell where you want the resulting category or comparison output to be displayed.

Input the nested IF formula

Type the formula =IF(A1>75/24,"ABC",IF(A1<70/24,"","XYZ")) into the formula bar, assuming your cumulative time value is located in cell A1.

Apply the calculation

Press Enter. The cell will now display 'ABC' if the time is strictly above 75 hours, 'XYZ' if it falls between 70 and 75 hours, and will remain blank if it is below 70 hours.

Use Day-Fraction Comparisons with the IF Function

Visualizing Cumulative Time: Always remember to format your duration cells with the custom format [h]:mm. The brackets force the spreadsheet to display totals exceeding 24 hours correctly instead of resetting.

Manage Time Data Effortlessly

Handle Cumulative Time Calculations Easily with WPS Spreadsheet

WPS Office provides a highly compatible spreadsheet tool that perfectly handles complex time formulas and formatting just like Excel. You can quickly apply cumulative time formats and nested IF functions with zero learning curve.

1. Open your workbook: Launch WPS Spreadsheet and open the file containing your time tracking data.
2. Format cells for cumulative time: Select your time data cells, right-click, and choose 'Format Cells'. Navigate to the 'Custom' category and input [h]:mm to display hours over 24 correctly.
3. Apply the threshold formula: Select the result cell and input the formula =IF(A1>75/24,"ABC",IF(A1<70/24,"","XYZ")) to evaluate the time accurately.

Fully compatible with Microsoft Excel formulas and time fraction calculationsIntuitive custom cell formatting interface for applying [h]:mmLightweight application with fast calculation speeds for large datasetsCompletely free to download and use for your daily office tasks

microsoft office alternative - wps office

Frequently Asked Questions

Why does TIMEVALUE("75:00") return 3:00 instead of 75 hours?

The TIMEVALUE function is designed to evaluate standard clock time and ignores full 24-hour day cycles. Thus, 75 hours is treated as 3 full days and 3 extra hours, causing the function to return only the remaining 3:00.

How do I display hours greater than 24 without them resetting to zero?

You need to apply a custom number format to the cell. Right-click the cell, select Format Cells, navigate to the Custom category, and type [h]:mm. The square brackets instruct the spreadsheet to display cumulative hours.

Can I use this day-fraction method for checking other time thresholds?

Yes. You can compare any cumulative time simply by dividing your target hour amount by 24. For example, to check if a value is greater than 100 hours, you would use A1>100/24 in your logical formulas.

Excel & Data Problems