How to Calculate Per-Diem Days with Min 5 and Max 7 in Excel
Question details
The user needs an Excel formula to calculate per-diem or paid days based on a specific logic: assigning a default minimum of 5 days, calculating the exact number if between 5 and 7 days are worked, and capping the maximum at 7 days.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Calculating weekly per-diem payouts or billable days from a timesheet, where employees are guaranteed 5 days but cannot claim more than 7.
- Observed behavior
- Requires a formula to output 5 for counts less than or equal to 5, the actual count for 6 or 7, and 7 for counts greater than 7.
Ensure your timesheet data is structured in a continuous row or column (e.g., cells G111 to M111 representing days of the week) with numeric values greater than zero indicating a worked day.
Use IF, MIN, and COUNTIF Functions
Combine logical and statistical functions to enforce the minimum and maximum caps dynamically based on your weekly data.
This approach counts the number of days with logged hours greater than zero. It then uses the IF function to return 5 if the count is 5 or less, and the MIN function to ensure the result never exceeds 7 if the count is higher.
Click on the empty cell where you want the final per-diem calculation to appear.
Type =IF(COUNTIF(G111:M111,">0")>5,MIN(COUNTIF(G111:M111,">0"),7),5) into the formula bar. Replace G111:M111 with your actual timesheet data range.
Hit the Enter key to apply the formula. It will now automatically calculate the per-diem days according to your minimum and maximum rules.
Use a Nested MAX and MIN Formula (Simplified Alternative)
Use a nested MIN and MAX function to strictly bind a number between a lower and upper limit without needing a complex IF statement.
Calculate Payroll and Per-Diem Seamlessly with WPS Spreadsheet
WPS Spreadsheet fully supports all standard Excel functions like IF, COUNTIF, MAX, and MIN, allowing you to manage timesheets and complex per-diem calculations with zero compatibility issues.
- 1. Open WPS Spreadsheet: Launch WPS Office and create a new spreadsheet or open your existing timesheet document.
- 2. Input your data: Enter the daily worked hours across columns for your employees.
- 3. Apply the formula: Use the =MAX(5, MIN(COUNTIF(range,">0"), 7)) formula in the designated total column to easily calculate the capped per-diem days.
Frequently Asked Questions
Why does my COUNTIF formula return 0 even though there is data?
This usually happens if the criteria is formatted incorrectly or the data contains text instead of numbers. Ensure your criteria ">0" includes the quotation marks and that the referenced cells contain actual numeric values, not text strings looking like numbers.
Can I change the minimum and maximum days dynamically instead of hardcoding them?
Yes. Instead of hardcoding 5 and 7 into your formula, you can reference specific cells (e.g., =MAX($A$1, MIN(COUNTIF(G111:M111,">0"), $B$1))) where A1 holds your minimum limit and B1 holds your maximum limit.
How do I count cells that contain specific text like "Present" instead of numbers?
You can easily modify the COUNTIF criteria. Replace ">0" with "Present" or "P" in your formula. For example: =MAX(5, MIN(COUNTIF(G111:M111,"Present"), 7)).
