How can I get the same date next month in Excel?

By /

To get the same date next month in Excel, you can use the EDATE function. This function takes in two arguments – the starting date and the number of months to add. So, if you have a date in cell A1, you can use the formula =EDATE(A1,1) to get the same date next month. This will add one month to the date in A1 and return the corresponding date in the next month. You can also use this function to get the same date in a future or past month by changing the number of months to add accordingly.

Often you may want to get the same date next month in Excel.

For example, if a given cell contains the date 1/1/2024 then you may want to use a formula to return 2/1/2024.

The easiest way to do this is by using the EDATE function as follows:

=EDATE(B2, 1)

This particular formula returns the same date next month for the date in cell B2.

The following example shows how to use this formula in practice.

Example: How to Get Same Date Next Month in Excel

Suppose we have the following dataset in Excel that shows the dates when various employees started working at some company:

Suppose we would like to get the same date next month for each date in the Start Date column.

We can type the following formula into cell C2 to do so:

=EDATE(B2, 1)

We can then click and drag this formula down to each remaining cell in column C:

Excel get same day next month

Column C returns the same day next month for each corresponding date in column B.

How This Formula Works

Recall the formula that we used to get the same day next month for the date in cell B2:

=EDATE(B2, 1)

This particular formula uses the EDATE function, which uses the following syntax:

EDATE(start_date, months)

where:

  • start_date: A date that represents the start date
  • months: The number of months to add to the start date

By using EDATE(B2, 1) we are able to add exactly one month to the date in cell B2.

Note #1: If you’d like, you can provide a negative value as the second argument in the EDATE function to instead subtract a certain number of months.

Note #2: You can find the complete documentation for the EDATE function in Excel .

Additional Resources

The following tutorials explain how to perform other common tasks in Excel:

x

Related terms:

  1. How to Use VLOOKUP to Return Value in Next Cell in Excel?
  2. Excel: Split Date into Day, Month and Year
  3. VBA: How do I get a month name from a date?
  4. How to convert date to month and year format in Google Sheets?
  5. Next-in-Line Effect
  6. Excel: Extract Date from Text String How do i extract a date from a text string?
  7. How do I convert between Julian Date and Calendar date in Excel?
  8. How to Convert Excel Date Format to Proper Date in R
  9. How to Sum by Month in Excel (Step-by-Step Example)
  10. How to Filter Dates by Month in Excel (With Example)