Formula Errors

How to Calculate Subsistence Costs by Elapsed Hours in Excel

Maira Mehtab Sep 22, 2026 869 views

Question details

The user needs an Excel formula to calculate claimable subsistence costs based on elapsed time, where a flat rate applies to the first 24 hours and a different hourly rate applies to any additional time.

Product: Excel
Device & OS: not provided
Scenario: Calculating employee or contractor expenses where reimbursement policies dictate a base flat fee for the first 24 hours and an additional hourly rate for subsequent time.
Observed behavior: The goal is to automatically output the correct total cost based on the total hours inputted (e.g., 31 hours equals the base 24-hour fee plus 7 times the additional hourly rate).

Before you start

Ensure that your elapsed time data is formatted correctly as a numerical value (representing total hours) rather than a specific time format, so the mathematical formula can properly calculate the thresholds.

Solution 1Recommended

Use an IF Function for Tiered Rates

Create a logical formula that checks if the elapsed hours exceed 24, applying the base rate plus the extra hourly rate only when necessary.

In a tiered cost scenario, such as a £35 base rate for the first 24 hours and a £7.50 hourly rate for any time beyond that, an IF statement can logically separate the calculation based on the total hours.

Set up your input cell

Enter the total elapsed hours into a cell in your spreadsheet. For this example, type '31' into cell A2.

Write the IF formula

In an adjacent cell where you want the total cost to appear, type the following formula: =IF(A2<=24, 35, 35+((A2-24)*7.50))

Apply and test the formula

Press Enter to see the result. Because A2 is 31, the formula calculates £35 + (7 * £7.50), returning a total of 42.50.

Using Cell References for Rates: Instead of typing '35' and '7.50' directly into the formula, it is best practice to put these rates in separate cells (e.g., B1 and B2) and use absolute references (like $B$1). This makes updating the rates much easier in the future.

Solution 2

Use the MAX Function as a Shorter Alternative

Use the MAX function to create a more streamlined formula that achieves the same tiered result without an IF statement.

Manage Expenses Easily with WPS Spreadsheet

Calculate Tiered Costs Seamlessly in WPS Office

WPS Spreadsheet fully supports advanced logical formulas like IF, nested IFs, and MAX functions, allowing you to build complex expense and subsistence calculators with ease. It is completely compatible with Microsoft Excel file formats.

1. Open WPS Spreadsheet: Launch WPS Office and open a new or existing expense tracking spreadsheet.
2. Input your expense data: Enter your total elapsed hours in one column, and establish your 24-hour base rate and additional hourly rate in clearly labeled cells.
3. Enter the formula: Type your logical calculation (e.g., =IF(hours<=24, base_rate, base_rate+(hours-24)*extra_rate)) directly into the total cost cell.
4. Format as Currency: Select your result cell, right-click, choose 'Format Cells', and select 'Currency' to properly display the financial amount.

Fully compatible with Microsoft Excel (.xlsx) formats and formula syntax.Free, lightweight, and fast-loading spreadsheet application.Familiar user interface for a smooth transition from other office suites.Extensive built-in function library to help you create complex tiered calculations quickly.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my formula returning an error or incorrect value?

Ensure that your elapsed time cell is formatted as a standard Number or General format rather than a Date or Time format. If the spreadsheet reads the cell as a time value, '31' might be interpreted as a date serial number rather than 31 total hours.

How do I calculate total elapsed hours from start and end dates/times?

You can subtract the start date/time cell from the end date/time cell (e.g., =B2-A2). Because spreadsheet software calculates dates as days, multiply the result by 24 (e.g., =(B2-A2)*24) to convert the fraction of days into total elapsed hours.

Can I add more than two tiers for subsistence rates?

Yes. If you have a third tier (for example, a different rate after 48 hours), you can use a nested IF statement (e.g., =IF(A2<=24, Rate1, IF(A2<=48, Rate2Calculation, Rate3Calculation))) or use the IFS function if your software version supports it.

Excel & Data Problems