'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Add Months to a Month and Year in Excel
Question details
The user wants to generate a chronological sequence of month and year values (e.g., Mar 2024, Apr 2024) but encounters formula errors like #NAME?, 0-Jan-00, or 2-1 when starting from text instead of actual dates.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Creating an automated row or column sequence of months and years for scheduling, forecasting, or calendar setups.
- Observed behavior
- Formulas return #NAME? errors, 0-Jan-00, or invalid formats when the reference cell is formatted as plain text or uses incorrect concatenation syntax.
Check your starting cell to see if it is formatted as a true Excel date or as plain text, as this will dictate which formula you need to use to generate your sequence.
Use EDATE for Standard Date Values
Use this method when your starting cell contains a real Excel date that is simply formatted to display only the month and year.
Excel handles dates as serial numbers. If your starting cell contains a real date (like March 1, 2024), you can easily add months to it using the EDATE function combined with TEXT to maintain the desired display format.
Select your starting cell (for example, A2) and look at the formula bar at the top of the screen to confirm it reads as a full date, such as 3/1/2024.
Click the adjacent cell where you want the next month to appear. Type the formula =TEXT(EDATE(A2,1),"mmm yyyy") and press Enter.
Select the cell with your new formula, click the small square at the bottom-right corner (the fill handle), and drag it right or down to automatically generate the remaining months.
Convert Text Strings to Dates Before Adding Months
Use this solution if your starting cell is plain text (e.g., typing 'Mar 2024' directly) and cannot be read as a standard date by Excel.
Calculate and Format Dates Seamlessly in WPS Spreadsheet
WPS Spreadsheet fully supports standard Excel date and time functions, including EDATE, TEXT, and DATEVALUE, allowing you to build automated schedules and sequences without encountering compatibility errors.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file where you need to build your date sequence.
- 2. Input the starting value: Type your initial date (e.g., 3/1/2024) into the first cell and format it to show the month and year.
- 3. Apply the EDATE formula: In the next cell, enter =TEXT(EDATE(A2,1),"mmm yyyy") to calculate the next sequential month.
- 4. Drag to complete: Use the fill handle at the corner of the cell to drag and generate the rest of the sequence automatically.
Frequently Asked Questions
Why is my formula returning a #NAME? error?
The #NAME? error occurs when Excel does not recognize a function name or encounters invalid syntax. Check your spelling for the EDATE and DATEVALUE functions, and ensure you use the ampersand (&) for concatenation instead of unsupported characters.
How do I add multiple months at a time instead of just one?
In the EDATE function, change the second argument from 1 to the exact number of months you want to add. For example, using =TEXT(EDATE(A2, 3), "mmm yyyy") will add three months to the starting date in cell A2.
Why does my next cell show 0-Jan-00?
This happens when the DATEVALUE function cannot interpret your text string as a real date. Ensure the text in your starting cell is formatted clearly (like 'Mar 2024') so that adding '1 ' creates a string ('1 Mar 2024') that the software can properly calculate.
Can I format the result to display the full month name?
Yes, you can alter the formatting string within the TEXT function. Use =TEXT(EDATE(A2,1),"mmmm yyyy") to display 'April 2024' or just "mmmm" to display the month name by itself.
