Next month in excel

Next month in excel DEFAULT

Get same date next month

One of the most commonly used formats in Microsoft Excel is dates. Over time, Excel has added some very cool features regarding dates. One such feature is to get the same date next month. We can do it using the EDATE function in Excel. In this tutorial, we will learn how to get same date next month in Excel.

Figure 1. Example of How to Get Same Date Next Month in Excel

Generic Formula


The EDATE function can generate same date next month. It uses two arguments: start_date and months. To get the same date in the future, we need to provide the date as start_date. We need to use 1. This will generate the same date next month.

Setting up Data

The following example contains some sample dates. They are in column A.

Figure 2. The Data Set

To find the same date next month:

  • We need to go to cell B2.
  • Next, we need to go to Formulas>Insert Function. In the search for a function box type EDATE and click Go.

Figure 3. Selecting the EDATE Function

  • In the select a function box, we need to click on EDATE and press Ok.
  • Next, we need to input the arguments. For the start_date we need to insert A2 and for months we need to type 1.

Figure 4. Setting the Arguments

  • Lastly, we need to click OK. We need to drag the formula using the fill handle from cells B2 to B6.

Figure 5. The Function in Action

This will show the same date next month in column B.


We can also find the same date previous month. To find the same date previous month for the last example, we need to assign the  formula =EDATE(A2,-1) to cell B2.

Figure 6. Get Same Date Previous Month

This will show the same date in the previous month in column B.

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our liveExcelchatservice! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.


How to get same or first day of next month based on given date in Excel?

This article is talking about getting same day or first day of next month based on given date in Excel. Please do as follows.

Getting same day of next month based on given date by formula
Getting first day of next month based on given date by formula

Getting same day of next month based on given date by formula

The following formula can help you to get same day of next month based on given date in Excel. Please do as follows.

1. Select a blank cell for locating the result, then enter formula =EDATE(A2,1) into the Formula Bar, and then press the Enter key. See screenshot:

Note: in the formula, A2 is the date cell you need to get same day of next month based on.

2. Keep selecting the result cell, then drag the Fill Handle down until all results populating.

3. Now you need to custom the result cells as date format. Keep selecting all the results, click the Number Format box, and then click Short Date from the drop-down list under the Home tab.

Note: For some special date such as /10/31, you will get the result as “/11/30” with above formula. Actually, date /10/31 does not exist in October. If you really want to get the same date of next month based on date /10/31, please use this formula =IF(DAY(EDATE(A2,1))<DAY(A2),NA(),EDATE(A2,1)) to get it down as below screenshot shown.

And you will get a #N/A error if the date does not exist in that month.

Getting first day of next month based on given date by formula

For getting the first day of next month based on given date in Excel, the following formula can help you.

Select a blank cell, enter formula =DATE(YEAR(A2),MONTH(A2)+1,1) into the Formula Bar and then press the Enter key. Then use the Fill Handle to get the rest dates. See screenshot.

The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns Prevent Duplicate Cells; Compare Ranges
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF
  • More than powerful features. Supports Office/Excel and Supports all languages. Easy deploying in your enterprise or organization. Full features day free trial. day money back guarantee.
kte tab

Read MoreFree DownloadPurchase 

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
  1. Pictures of sled
  2. Xbox windows app
  3. Huntingdon county 911
  4. Craigslist nc boats charlotte

Excel Formula > Selecting specific date of next month

I am trying to set up an automatic cell in Excel to show the same day of the Next Month based on the current month.

In other words, if today is 11/20/, and I want to show the 13th day of the Next month, I'd want the cell to end up showing 12/13/

I know that EDATE can help me calculate 1 month into the future based on a start date, but I dont want to have to change the start date on my spreadsheet every month.

To give a little background, this is basically a spreadsheet for paying bills. Some bills are due on the day day each month. I'd like to auto calculate the actual due date, and then use conditional formatting to highlight which bills are due within the next 14 days. I think I have the conditional formatting piece ready, I just dont know how to calculate the dates as I laid out above.

asked Nov 20 '20 at

Tom DTom D

bronze badges

Operations Management using Excel: Seasonality and Trend Forecasting

This article describes the formula syntax and usage of the EOMONTH function in Microsoft Excel.


Returns the serial number for the last day of the month that is the indicated number of months before or after start_date. Use EOMONTH to calculate maturity dates or due dates that fall on the last day of the month.


EOMONTH(start_date, months)

The EOMONTH function syntax has the following arguments:

  • Start_date    Required. A date that represents the starting date. Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(,5,23) for the 23rd day of May, Problems can occur if dates are entered as text.

  • Months    Required. The number of months before or after start_date. A positive value for months yields a future date; a negative value yields a past date.

    Note: If months is not an integer, it is truncated.


  • Microsoft Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, is serial number 1, and January 1, is serial number because it is 39, days after January 1,

  • If start_date is not a valid date, EOMONTH returns the #NUM! error value.

  • If start_date plus months yields an invalid date, EOMONTH returns the #NUM! error value.


Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.







Date of the last day of the month, one month after the date in A2.



Date of the last day of the month, three months before the date in A2.



Month excel next in

A formula to show Next month

On Fri, 10 Jun , AndreL <[email protected]>

>I looking for a fuction that show next month and not the current month. Cause
>the formula I have show the current month which is "=TODAY()". So your help
>will be appreciated.

What do you mean by "next month".

With the date in A1:

The following formula will show the NAME of the next month:


The following formula will add one month to the date, but not go past the last
day of the subsequent month.


If the EDATE function is not available, and returns the #NAME? error, install
and load the Analysis ToolPak add-in.

On the Tools menu, click Add-Ins.
In the Add-Ins available list, select the Analysis ToolPak box, and then click
If necessary, follow the instructions in the setup program.

Or, without installing the ATP:



Get current month in Excel using the CHOOSE, MONTH and TODAY functions


You will also be interested:


874 875 876 877 878