Function Problems

How to Find the Next Upcoming Date from Text Dates in Excel

Nimra Malik Sep 27, 2026 869 views

Question details

The user needs to calculate the next upcoming occurrence of a date based on a text string that contains only a day and month.

How to Find the Next Upcoming Date from Text Dates in Excel

Product: Excel
Device & OS: not provided
Scenario: Converting incomplete text dates (lacking a specific year) into fully valid dates that accurately reflect the next upcoming occurrence relative to today.
Observed behavior: The user has text dates representing only the day and month, and needs a formula to dynamically assign either the current year or the following year depending on whether the date has already passed in the current year.

Before you start

Ensure your text dates are formatted consistently (for example, '15-Mar' or 'March 15') so that the formula can correctly recognize and convert them into date values.

Solution 1Recommended

Use LET, DATEVALUE, and EDATE Functions

By combining the LET, DATEVALUE, and EDATE functions, you can check if a given day and month have already passed this year, and automatically add 12 months if they have.

This formula uses DATEVALUE to convert the text into a serial date for the current year. It then checks if that date is less than TODAY(). If it is, the EDATE function adds 12 months to push the date to next year; otherwise, it leaves it in the current year.

Select the target cell

Click on the empty cell where you want the calculated upcoming date to appear (for example, cell B2 if your text date is in A2).

Enter the formula

Type the formula =LET(d,DATEVALUE(A2),EDATE(d,12*(d<TODAY()))) into the formula bar and press Enter.

Format the result as a date

Right-click the result cell, select 'Format Cells', navigate to the 'Date' category, and pick your preferred date display format.

Fill the formula down

Click and hold the small square at the bottom-right corner of cell B2, then drag it down the column to apply the calculation to the rest of your text dates.

Alternative Formula: If your software version does not support the LET function, you can achieve the exact same result using an IF statement: =IF(DATEVALUE(A2)<TODAY(), EDATE(DATEVALUE(A2), 12), DATEVALUE(A2)).

Solve with WPS Spreadsheet

Calculate Future Dates Easily with WPS Spreadsheet

WPS Spreadsheet fully supports advanced mathematical and date functions, including LET, DATEVALUE, and EDATE. You can easily manage and calculate upcoming schedules without worrying about syntax errors.

1. Open your dataset in WPS Spreadsheet: Launch WPS Office, open Spreadsheet, and load the workbook containing your text dates.
2. Input the dynamic date formula: Select the cell next to your text date and type =LET(d,DATEVALUE(A2),EDATE(d,12*(d<TODAY()))).
3. Format and apply: Right-click to format the cell as a Date, then double-click the fill handle to populate the remaining rows instantly.

Fully compatible with Microsoft Excel formulas and date formatsBuilt-in advanced date and time function libraryIntuitive UI for cell formatting and data draggingFree and lightweight spreadsheet solution

Frequently Asked Questions

Why am I getting a #VALUE! error when using DATEVALUE?

The #VALUE! error occurs when the text in the cell is not in a recognizable date format. Ensure your text is typed in a standard way, such as 'Jan 15' or '15-Jan', so the DATEVALUE function can properly interpret the string as a date.

How does the formula know to add a year if the date has passed?

The expression (d<TODAY()) checks if the date is earlier than today's date. If it is true, the expression evaluates to 1, and 12 * 1 tells the EDATE function to add 12 months (one year). If false, it evaluates to 0, adding 0 months.

Can I solve this without the LET function?

Yes, if you are using an older version of Excel that doesn't support the LET function, you can use a standard IF function: =IF(DATEVALUE(A2)<TODAY(), EDATE(DATEVALUE(A2), 12), DATEVALUE(A2)).

Excel & Data Problems