Function Problems

How to Create Excel Formulas for Current and Future Monthly Date Ranges

Maira Mehtab Sep 22, 2026 869 views

Question details

The user needs to display the first and last day of the current month and the next three months as formatted text ranges using Excel formulas.

Product: Excel
Device & OS: not provided
Scenario: Generating continuous monthly date ranges (start date to end date) based on today's date or a specific invoice date.
Observed behavior: The goal state is a text string formatted like "mm/dd/yyyy - mm/dd/yyyy" for the current month and subsequent months.

Before you start

Ensure your system date is correct if you are using the TODAY() function. Alternatively, prepare a dedicated cell containing your base invoice date if you prefer not to use the current date.

Solution 1Recommended

Use EOMONTH and TEXT for Individual Months

Combine EOMONTH with the TEXT function to dynamically calculate the first and last days of any month and display them as a single string.

The EOMONTH function easily finds the last day of a month by adding or subtracting months from a starting date. By wrapping it in the TEXT function, you can format the dates exactly as you want and combine them into a single cell.

Select the target cell

Click on the cell where you want the current month's date range to appear.

Enter the formula for the current month

Type the following formula: =TEXT(EOMONTH(TODAY(),-1)+1,"mm/dd/yyyy") & " - " & TEXT(EOMONTH(TODAY(),0),"mm/dd/yyyy") and press Enter.

Calculate the next month's range

In the cell below, adjust the EOMONTH offsets by adding 1 to get the next month: =TEXT(EOMONTH(TODAY(),0)+1,"mm/dd/yyyy") & " - " & TEXT(EOMONTH(TODAY(),1),"mm/dd/yyyy").

Repeat for future months

Continue incrementing the offsets (e.g., 1 and 2, then 2 and 3) to generate formulas for the subsequent months.

Using a Specific Invoice Date: Replace TODAY() with a cell reference (like A1) if you want to base the date ranges on a specific invoice date instead of the current system date.

Solution 2

Use SEQUENCE for Multiple Months Simultaneously (Microsoft 365)

If you use Microsoft 365, you can generate all four date ranges at once using a single dynamic array formula.

Powerful Spreadsheet Tool

Generate Dynamic Date Ranges Effortlessly in WPS Spreadsheet

WPS Office Spreadsheet fully supports advanced date functions like EOMONTH, TEXT, and dynamic arrays. You can use the exact same formulas to calculate your monthly date ranges for free.

1. Open WPS Spreadsheet: Launch WPS Office and open a new or existing spreadsheet document.
2. Select your cell: Click on the cell where you wish to display your formatted date range.
3. Enter the formula: Paste the EOMONTH and TEXT formula exactly as you would in Microsoft Excel.
4. Press Enter: Hit Enter to instantly calculate and perfectly format your current or future monthly date range.

100% compatible with Microsoft Excel formulas and file formatsFully supports advanced date, time, and dynamic array functionsFree and lightweight alternative for professional spreadsheet managementFamiliar user interface requires absolutely no learning curve

microsoft office alternative - wps office

Frequently Asked Questions

Can I change the date format from mm/dd/yyyy to dd-mm-yyyy?

Yes, simply change the format string inside the TEXT function. For example, use =TEXT(EOMONTH(TODAY(),-1)+1,"dd-mm-yyyy") to display the date in day-month-year format.

How do I calculate the previous month's date range?

To get the prior month, adjust the EOMONTH offsets backwards. Use -2 and -1 in the formula: =TEXT(EOMONTH(TODAY(),-2)+1,"mm/dd/yyyy") & " - " & TEXT(EOMONTH(TODAY(),-1),"mm/dd/yyyy").

Why is my formula displaying as a number instead of a date?

If you omit the TEXT function, Excel evaluates dates as serial numbers. Ensuring you wrap the EOMONTH calculation in a TEXT function forces Excel to display it as a readable date string.

Will the dynamic SEQUENCE formula work in older versions of Excel?

No, the SEQUENCE function is only available in Microsoft 365 and Excel 2021. For older versions like Excel 2016 or 2019, you must use the individual EOMONTH formulas for each row.

Excel & Data Problems