Excel: How to Get Same Date Next Month


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 here.

Additional Resources

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

Excel: How to Calculate Age on a Specific Date
Excel: How to Check if Date is Between Two Dates
Excel: How to Use VLOOKUP by Date

Leave a Reply

Your email address will not be published. Required fields are marked *