Table of Contents
To get the first day of the next month in Excel, you can use the EOMONTH function. This function takes two arguments- a start date and a number of months. By setting the start date to the current date and the number of months to 1, the EOMONTH function will return the last day of the current month. To get the first day of the next month, you can use the DATE function to add 1 day to the result of the EOMONTH function. The final formula would look like this: =DATE(YEAR(EOMONTH(TODAY(),1)),MONTH(EOMONTH(TODAY(),1))+1,1). This will return the first day of the next month based on the current date.
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:
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 .
Additional Resources
The following tutorials explain how to perform other common operations in Excel: