Excel: How to Get First Day of Next Month


You can use the following formula to get the first day of the next month in Excel:

=EOMONTH(A2, 0)+1

This particular formula returns the first day of the next month for the date in cell A2.

For example, if cell A2 contains the date 1/5/2024 then this formula would return 2/1/2024.

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

Example: How to Get First Day of Next Month in Excel

Suppose we have the following dataset in Excel that contains information about sales made on various dates at some company:

Suppose that we would like to find the first day of the next month for each date in the Date column.

To do so, we can type the following formula into cell C2:

=EOMONTH(A2, 0)+1

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

Excel get first day of next month

Column C now shows the first day of the next month for each corresponding date in column A.

For example:

  • The first day of the next month for 1/5/2024 is 2/1/2024.
  • The first day of the next month for 1/18/2024 is 2/1/2024.
  • The first day of the next month for 2/12/2024 is 3/1/2024.

And so on.

How This Formula Works

Recall the formula that we used to find the first day of the next month for cell A2:

=EOMONTH(A2, 0)+1

Here is how this formula works:

The EOMONTH function in Excel returns the last day of the month that is the indicated number of months before or after some date.

By using EOMONTH(A2, 0) we are able to find the last day of the month for the date in cell A2.

Then, we simply add 1 to get the first day of the next month.

Note: You can find the complete documentation for the EOMONTH function in Excel here.

Additional Resources

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

How to Get First Day of Week in Excel
How to Get First Day of Quarter in Excel
How to Find First Monday of Each Month in Excel

Leave a Reply

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