Calculation Issues

Calculate Year-over-Year Change Missing Exact Date Data in Spreadsheets

Olivia MillerOlivia Miller Nov 11, 2026 869 views

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.

How to Calculate Year-over-Year Change When Exact Prior Dates Have No Data
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.
Before you start

Ensure your dataset is sorted correctly and create a named range called 'Holidays' containing the specific dates of all relevant public holidays.

Solution 1Recommended

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.

1
Calculate the previous business day

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.

2
Retrieve the prior-year value

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.

3
Calculate the YoY percentage change

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.

Use INDEX, XMATCH, and WORKDAY Formulas to Find Prior-Year Business Values
Formula Breakdown: This formula assumes Column L contains your dates, Column C has the values, and Column B contains row identifiers. XMATCH processes these as arrays to ensure both conditions are met simultaneously.
Advanced Spreadsheet Functions

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. 1. Open WPS Spreadsheet: Launch WPS Office and open your financial or operational data workbook.
  2. 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. 3. Apply the YoY formula: Enter the combined INDEX and WORKDAY formula into your percentage change column to calculate accurate historical comparisons automatically.
Fully compatible with Microsoft Excel formulas and array functionsAdvanced data analysis and conditional formatting toolsFast, lightweight application suitable for handling large datasetsFree to use with comprehensive cross-platform support
microsoft office alternative - wps office

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.