Function Problems

How to Use an Excel Formula to Pull Values Based on a Month and Date Range

Maira Mehtab Sep 28, 2026 869 views

Question details

The user needs an Excel formula to populate cells across multiple rows and columns by checking if a specific month and year fall within a given start and end date range.

Product: Excel
Device & OS: not provided
Scenario: Distributing or extracting specific values (e.g., from cell D9) into a multi-column and multi-row layout conditionally based on date boundaries.
Observed behavior: The target cell should display a specific value when the date condition is met, and remain blank when the month falls outside the specified boundaries.

Before you start

Ensure your reference cells containing the month/year and the start/end bounds are formatted as actual Dates in Excel, not as text, to allow the EOMONTH function to calculate correctly.

Solution 1Recommended

Use a Combination of IF, AND, and EOMONTH Functions

This solution uses a combined formula to check if the first and last day of a specific month fall within your target date range, returning the desired value if true.

The EOMONTH function is highly effective for date-range testing because it allows you to dynamically find the last day of a given month. By adding 1 to the previous month's end date, you easily retrieve the first day of the current month. Using this alongside the IF and AND functions lets you create a boundary check.

Select the target cell

Click on cell F9 where you need the formula to display the first result.

Enter the date-range formula

Type the formula =IF(AND(EOMONTH($B9,0)>=$C$4,EOMONTH($B9,-1)+1<=$F$4),$D9,"") into the formula bar.

Apply the formula

Press Enter. The cell will display the value from D9 if the month overlaps with the date range, or remain blank if it does not.

Copy across rows and columns

Click the fill handle (the small square at the bottom-right of cell F9) and drag it down 45 rows, then drag it across 12 columns to apply the logic to the rest of your dataset.

Understanding Reference Locking: The dollar signs ($) in the formula lock specific rows or columns. Make sure $C$4 and $F$4 are locked entirely if your date range bounds are always stored in those exact cells, while $B9 locks only the column so the row can adapt as you drag down.

Advanced Spreadsheet Features

Easily Calculate Date Ranges with WPS Office

WPS Spreadsheet fully supports advanced date-range functions like EOMONTH, IF, and AND. It offers a smooth, lightweight experience for all your complex data analysis and conditional formatting needs.

1. Open your workbook: Launch WPS Spreadsheet and open the document containing your date data.
2. Insert the formula: Select your target cell (e.g., F9) and type the combined IF and EOMONTH formula.
3. Drag to fill: Use the fill handle at the bottom right of the cell to easily apply the formula across your desired 45 rows and 12 columns.

100% compatible with Microsoft Excel formulas and date formats.Intuitive formula builder with built-in syntax highlighting to prevent errors.Lightweight software with fast processing for large datasets and complex logic.Free built-in templates for project management, timelines, and financial tracking.

Frequently Asked Questions

Why is my EOMONTH formula returning a #VALUE! error?

This usually happens if the referenced cell contains text instead of a valid date. Check your reference cells (B9, C4, F4) and ensure they are formatted as Dates. You can change the format by right-clicking the cell, selecting 'Format Cells', and choosing 'Date'.

How does EOMONTH($B9,-1)+1 calculate the first day of the month?

The EOMONTH function with the '-1' argument finds the very last day of the previous month. By adding 1 to that date, you mathematically step forward one day, which accurately provides the first day of the current month listed in cell B9.

Can I adapt this formula for different date range cells?

Yes. Simply update the absolute cell references ($C$4 and $F$4) in the formula to point to your new start and end date cells. Ensure you keep the dollar signs so the references do not shift when you copy the formula across other cells.

Excel & Data Problems