How to Calculate Excel Costs Using Weekdays, Sundays, and Bank Holidays in WPS Office
Question details
The user needs to create an Excel-compatible staffing cost formula that applies a regular hourly rate or a holiday hourly rate depending on whether the event date is a Sunday or a listed bank holiday.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is building a staffing cost model where each event date must be compared with weekday, Sunday, and bank holiday conditions so the correct resource rate is selected from regular or holiday rate tables.
- Observed behavior
- The desired result is a schedule in which each cell automatically multiplies the scheduled hours by the correct regular or holiday rate based on the event date.
Before you start, organize your regular rates, holiday rates, and bank holiday dates into separate tables or named ranges, and confirm that all event dates are real Excel date values rather than text.
Use Structured Tables with MATCH and INDEX to Apply Regular or Holiday Rates
Use this method when your rate lists and bank holiday list may grow; structured tables expand automatically and the formula stays readable.
This formula checks whether the event date in column B is found in the BankHolidays table or whether column C is Sunday. If neither condition is true, it takes the regular rate from the RegularRate table; otherwise it takes the holiday rate from the HolidayRate table.
In WPS Spreadsheets, select the regular rate data including the header row, click Insert > Table, check "My table has headers," and name the table RegularRate using Table Tools. Repeat for the holiday rate data and name it HolidayRate.
Select the bank holiday dates with the header "Bank Holidays," click Insert > Table, check "My table has headers," and name the table BankHolidays.
Click the cell where the first cost result should appear, paste =E8*IF(ISNA(MATCH($B8,BankHolidays[Bank Holidays],0))*($C8<>"Sunday"),INDEX(RegularRate[Cost / Hourly Rate],MATCH(E$6,RegularRate[Resource],0)),INDEX(HolidayRate[Cost / Hourly Rate],MATCH(E$6,HolidayRate[Resource],0))), and press Enter.
Drag the fill handle or copy the cell and paste it into the remaining schedule cells to calculate costs for all resources and event dates.
Use a Range-Based Formula with WEEKDAY, MATCH, and VLOOKUP
Use this alternative when you prefer ordinary ranges instead of Excel tables, but remember to adjust every range manually when data is added.
Build a Holiday and Sunday Staffing Rate Calculator in WPS Office Spreadsheets
WPS Office Spreadsheets supports Excel-compatible formulas, structured tables, VLOOKUP, INDEX, and MATCH, so you can create a reliable staffing cost calculator that selects regular or holiday rates based on Sundays and bank holidays.
- 1. Open WPS Office Spreadsheets: Launch WPS Office and create a new blank spreadsheet or open your existing staffing schedule workbook.
- 2. Create the rate and bank holiday tables: Select your regular rate data, click Insert > Table, and create RegularRate, HolidayRate, and BankHolidays tables.
- 3. Apply the recommended cost formula: In the first schedule cost cell, enter the MATCH/INDEX formula that checks the event date against Sundays and bank holidays, then press Enter.
- 4. Test and copy the formula: Verify the formula returns regular rates on normal weekdays and holiday rates on Sundays or listed bank holidays, then drag to fill the remaining schedule.
- 5. Save as an Excel-compatible workbook: Click Menu > Save As, choose .xlsx format, and save the file so it remains compatible with Microsoft Excel.
Frequently Asked Questions
Why does the formula return the holiday rate for every date?
Check that the BankHolidays dates are actual date values and not text. In WPS Spreadsheets, select the holiday column, open Format Cells, and use a Date format. You can also use ISNUMBER on a holiday cell to confirm it is a real date.
Can I keep the bank holiday list on another sheet without using a structured table?
Yes. Use a sheet-qualified range such as Variables!$G$6:$G$15 in the MATCH function. Keep the reference absolute so it does not change when you copy the formula, and expand the range manually when the list grows.
How does the formula identify Sundays?
The range-based formula uses WEEKDAY($B8,1)=1, where return type 1 treats Sunday as the first day of the week. If your sheet already has a weekday name in column C, the structured-table version can use $C8<>"Sunday" instead.
What should I do when I add more resources or bank holidays later?
If you used structured tables, add the new row directly below the last table row and the table reference expands automatically. If you used ordinary ranges, update the VLOOKUP and MATCH ranges in every formula to include the new cells.
Does this calculation work when the workbook is opened in Microsoft Excel?
Yes. WPS Office Spreadsheets supports the same formulas, structured references, VLOOKUP, INDEX, and MATCH, and saving the file as .xlsx preserves the calculation and compatibility with Microsoft Excel.
