Background
In this article, I will show you
- How to Get the First day of Next Month?
- How to find First day of Next Month?
- How to find 1st day of Next Month?
Sample Workbook
Download the workbook to practice it by yourself!
Press the download button!
Formula 1
Formula of cell B2
=DATE(YEAR(A2),MONTH(A2)+1,1)
DATE function Syntax:
=DATE(year, month, day)
Explanation
DATE function takes into 3 arguments. When you input year, month and day, it will form a date.
Take Cell A2 as an example, the value of Cell A2 is 24th April, 2019.
YEAR function gets the year of Cell A2, which is 2019.
MONTH function gets the month of Cell A2, which is 4.
DAY function gets the month of Cell A2, which is 24.
If you applies date function to Cell A2, it will remain the same value, which is 24th April, 2019.
However, what we are trying to find here is the First day of Next Month. So we have to edit the formula a bit.
=DATE(YEAR(A2),MONTH(A2)+1,1)To get the next month, we will add 1 to the current month.
=DATE(YEAR(A2),MONTH(A2)+1,1)
To get the first day of each month, we will put 1 here.
Formula 2
Formula of cell B2
=EOMONTH(A2,0)+1
EOMONTH function Syntax:
=EOMONTH(start_date, months)
EOMONTH function calculates the last day of the month.
Explanation
Take Cell A2 as an example, the value of Cell A2 is 24th April, 2019.
Applying EOMONTH function to 24th April, 2019, it will return the last day of April, which is 30th April, 2019.
However, getting the last day of current month is not what we want.
What we want is the first day of next month.
So, we have to add 1 to the last day of current month.
Do you find this article helpful? Subscribe to our newsletter to get exclusive Excel tips!
