Calculation Issues

How to Calculate Subsistence Allowance Based on Hours in Excel

Aamir Naveed Akram Sep 28, 2026 871 views

Question details

The user needs an Excel formula to calculate subsistence costs derived from total elapsed hours, properly separating full 24-hour periods from remaining partial hours.

How to Calculate Subsistence Allowance Based on Hours in Excel

Product: Excel
Device & OS: not provided
Scenario: Calculating travel subsistence or employee expense claims based on trip duration.
Observed behavior: The formula needs to calculate full 24-hour blocks at one rate (e.g., £35) and the remaining hours at another rate (e.g., £7.50 for 7 hours), ensuring 31 hours totals £42.50.

Before you start

Before creating the formula, ensure you have a clear table of your company's subsistence rate boundary rules (e.g., under 5 hours, 5-8 hours, 8-15 hours) to accurately reference in your calculation.

Solution 1Recommended

Calculate Allowances Using QUOTIENT and MOD Functions

Use this method to easily separate full 24-hour days from the remaining partial hours for accurate expense calculations.

Excel's QUOTIENT function helps you extract the number of full 24-hour periods without any decimals, while the MOD function isolates the remaining leftover hours. You can then multiply these components by their respective rates.

Prepare your data

In cell A2, enter the total elapsed hours for the trip (for example, type 31).

Calculate full 24-hour periods

In cell B2, enter the formula =QUOTIENT(A2, 24)*35 to calculate the allowance for full days, assuming your daily rate is £35.

Calculate remaining partial hours

In cell C2, enter the formula =MOD(A2, 24) to find the leftover hours. You can use an IF statement to apply the partial rate, such as =IF(MOD(A2,24)>0, 7.50, 0).

Combine into a single formula

To calculate the entire subsistence allowance in one cell, use the combined formula: =(QUOTIENT(A2,24)*35) + IF(MOD(A2,24)>=5, 7.50, 0). Adjust the '>=5' condition based on your exact hourly boundary rules.

Calculate Allowances Using QUOTIENT and MOD Functions

Handling Complex Boundary Rules: If you have multiple hourly brackets (e.g., different rates for 5-8 hours vs. 8-15 hours), replace the IF function with a VLOOKUP referencing a tiered rate table.

Solution 2

Use the INT Function for Time Calculations

An alternative approach utilizing standard division and the INT function to extract full days.

Efficient Spreadsheet Calculations

Calculate Travel Expenses Easily with WPS Spreadsheet

WPS Office provides a powerful, free Spreadsheet tool that fully supports all standard Excel formulas, including QUOTIENT, MOD, INT, and nested IF statements. You can easily manage employee expense claims and subsistence calculations with its intuitive interface.

1. Open WPS Spreadsheet: Launch WPS Office and create a new Spreadsheet or open your existing travel expense workbook.
2. Enter the Formula: Select the target cell and input the exact same QUOTIENT and MOD formulas used in Excel to automatically calculate your daily and hourly allowances.
3. Format as Currency: Select the result cells, right-click to choose 'Format Cells', and apply the Currency format for clear financial reporting.

Fully compatible with Microsoft Excel formulas and .xlsx files.Built-in robust functions for time tracking, data lookup, and conditional calculations.Free and lightweight, running smoothly on Windows, Mac, and Linux.

microsoft office alternative - wps office

Frequently Asked Questions

How do I handle different rates for multiple hour brackets in my formula?

Instead of a simple IF function, you should create a small lookup table on a separate sheet with your hour boundaries (e.g., 0, 5, 8, 15) and their corresponding rates. Use the VLOOKUP function with an approximate match (set the final argument to TRUE) on the remaining hours calculated by MOD(A2, 24).

Why is my time calculation returning a fraction instead of whole numbers?

If your elapsed time is formatted as a Date/Time value rather than a standard number of hours, spreadsheet software treats 1 day as 1.0. You will need to multiply the cell value by 24 and format the cell as a standard 'Number' or 'General' to get the true total hours.

Can I automatically calculate the total elapsed hours from a Start Date and End Date?

Yes. You can simply subtract the Start Date/Time from the End Date/Time (for example, =B2-A2). To convert this duration into total hours for your allowance formula, multiply the result by 24 (e.g., =(B2-A2)*24) and change the cell format to General or Number.

Excel & Data Problems