How to Return Previous and Current Month Values in Excel
Question details
The user wants to dynamically retrieve monthly project values based on the current date and calculate monthly project progress using elapsed and total workdays.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Tracking project performance dynamically by comparing current month values against previous month values, calculating the delta, and analyzing progress based on workdays.
- Observed behavior
- The user needs a dynamic set of formulas utilizing INDEX, MATCH, XLOOKUP, and NETWORKDAYS to automatically update tracking metrics based on today's date without manual adjustments.
Ensure your project values are organized with continuous monthly dates formatted as actual date values (e.g., the 1st of each month) in a single row to allow INDEX and MATCH formulas to function correctly.
Use INDEX and MATCH to Retrieve Monthly Values
This method uses the TODAY() function combined with INDEX and MATCH to dynamically look up values for the current and previous months without manual formula updates.
By setting the day value to 1 in the DATE function, we can dynamically match the first day of the current or previous month with the headers in your dataset.
Assuming your monthly dates are in cells L1 to P1 and the values are in L2 to P2, select the cell for the previous month's value and enter: =INDEX($L2:$P2,1,MATCH(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1),$L$1:$P$1,0))
In the cell designated for the current month's value, enter the formula: =INDEX($L2:$P2,1,MATCH(DATE(YEAR(TODAY()),MONTH(TODAY()),1),$L$1:$P$1,0))
To find the difference between the two months, subtract the previous month's value from the current month's value. For example: =CurrentMonthPV-PreviousMonthPV
Calculate Monthly Progress Using NETWORKDAYS
Use the NETWORKDAYS function to determine the ratio of elapsed workdays to total workdays in the month, effectively calculating your monthly progress.
Track Monthly Project Values Dynamically in WPS Spreadsheet
WPS Spreadsheet fully supports advanced array formulas, date lookups, and functions like INDEX, MATCH, XLOOKUP, and NETWORKDAYS to help you track project progress easily and accurately.
- 1. Open Your Spreadsheet: Launch WPS Office and open your project tracker spreadsheet.
- 2. Enter the Lookup Formula: Select the target cell and enter the INDEX/MATCH formula to fetch your current month's value.
- 3. Apply Workday Calculations: Use the =NETWORKDAYS() function in adjacent cells to calculate elapsed workdays against total workdays.
- 4. Format and Save: Format your progress cell as a percentage and save the document. Your dashboard will now update automatically.
Frequently Asked Questions
Why does my MATCH function return an #N/A error with dates?
Dates in spreadsheets are stored as serial numbers. Ensure your lookup array (the header row) contains actual date values formatted as dates (e.g., the 1st of the month) rather than plain text strings.
Can I use XLOOKUP instead of INDEX and MATCH for this task?
Yes, XLOOKUP is a simpler alternative if your software version supports it. For example: =XLOOKUP(DATE(YEAR(TODAY()),MONTH(TODAY()),1),$L$1:$P$1,$L$2:$P$2) accomplishes the same current-month lookup.
How do I handle weekends and holidays in the NETWORKDAYS function?
The NETWORKDAYS function automatically excludes standard weekends (Saturday and Sunday). To exclude specific holidays, supply a range containing holiday dates as the third argument: =NETWORKDAYS(start_date, end_date, [holidays]).
