Calculate Year-over-Year Change Missing Exact Date Data in Spreadsheets
Question details
Calculate the annual percentage change for daily data that excludes weekends and holidays, requiring a fallback to the most recent prior business date.

- Product
- WPS Spreadsheet / Microsoft Excel
- Device & OS
- not provided
- Scenario
- Performing year-over-year financial or performance analysis on datasets that only contain records for valid business days.
- Observed behavior
- Comparing exactly 12 months back often targets a weekend or public holiday, resulting in missing data errors and broken YoY calculations.
Ensure your dataset is sorted correctly and create a named range called 'Holidays' containing the specific dates of all relevant public holidays.
Use INDEX, XMATCH, and WORKDAY Formulas to Find Prior-Year Business Values
Combine the INDEX, XMATCH, WORKDAY, and EDATE functions to locate the nearest prior business day and calculate the percentage change accurately.
When calculating year-over-year growth for business metrics, comparing exact dates often fails due to leap years, weekends, and shifting holidays. By nesting the WORKDAY and EDATE functions, you can instruct the spreadsheet to automatically roll back to the nearest valid business day from exactly one year ago.
Use the formula WORKDAY(EDATE($L2,-12)+1,-1,Holidays) to shift your current date back exactly 12 months, effectively skipping weekends and any dates listed in your 'Holidays' named range.
Use the formula =INDEX($C$2:$C$5000, XMATCH(1, ($B$2:$B$5000=$B2) * ($L$2:$L$5000=WORKDAY(EDATE($L2,-12)+1,-1,Holidays)))) to match both your specific identifier and the newly adjusted business date to pull the correct historical value.
Divide your current value by the retrieved prior-year value and subtract 1. The complete formula in your target cell should be: =C2/INDEX($C$2:$C$5000, XMATCH(1, ($B$2:$B$5000=$B2) * ($L$2:$L$5000=WORKDAY(EDATE($L2,-12)+1,-1,Holidays)))) - 1.

Calculate Complex YoY Metrics Easily with WPS Spreadsheet
WPS Office provides robust formula support, including advanced functions like XMATCH, WORKDAY, and EDATE. You can seamlessly manage financial calculations, handle missing data, and perform complex YoY analysis with zero hassle.
- 1. Open WPS Spreadsheet: Launch WPS Office and open your financial or operational data workbook.
- 2. Define your Holiday range: List your holiday dates in a separate column, highlight them, and type 'Holidays' into the Name Box to define the range.
- 3. Apply the YoY formula: Enter the combined INDEX and WORKDAY formula into your percentage change column to calculate accurate historical comparisons automatically.

Frequently Asked Questions
What does the EDATE function do in this calculation?
The EDATE function calculates the exact same date a specified number of months in the past or future. By using EDATE($L2, -12), the formula effortlessly determines the date exactly 12 months prior to your current data point.
Why use XMATCH instead of the standard MATCH function?
XMATCH is a more modern, flexible function that defaults to exact matching and simplifies complex array queries. It allows you to multiply logical arrays—like matching both an identifier and a specific date simultaneously—without requiring legacy Ctrl+Shift+Enter array entry.
How do I create the 'Holidays' named range in WPS Spreadsheet?
Select the cells containing your list of custom holiday dates. Click on the Name Box located to the left of the formula bar, type the word 'Holidays', and press Enter. This range can now be referenced globally in any WORKDAY or NETWORKDAYS formula.




