Easily get the first day of the month in excel using this simple functions. We have discussed two simple methods in this article. Learn more about it here…
Finding the First Day of the Month in Excel
It is possible to settle automatically the First Day of Any Month. Although, there is no an specific function for returning the first day, this Tutorial will present how to adapt other functions related to dates to achieve this objective.
What is the First day of the month for this Date in Cell B3?
There are 2 methods: one through Eomonth Function and other with Date Function.
Method One: Using EOMONTH Function
We know that The Excel EOMONTH function returns the last day of the month, n months in the past or future. This function delivers a number that represents the last day of the month after or before specified amount.
The first argument is Start Date, which is the date which will be used as basis.
The second is Months. This is the amount of months before of after the Start Date that will define the period that we need to calculate the last day.
For example, if we need to calculate the last day within the previous month (before the Start Date), we need to subtract 1 from the Month argument. it would be -1.
In cell B5, apply the EOMONTH Function, using B3 as Start Date and -1 as Months argument.
The function returned the last day of January 2019, however our objective is the first day of February.
Press F2 in cell B5 and put +1 after in the end of the function, which literally adding one day to the returned serial number. Since the returned serial number represents the last day of the previous month, then, adding 1 will return the First day of the next month, and that’s our goal.
Now, it returns the first day of our Start Date.
Method Two: Using Date Function
This function works by building up a date in the future or in the past by adding to or subtracting from any argument of the function.
The arguments of Date Function are the components of a Date.
Since the objective is to return the First Day, the function will be settled differently.
Apply the Date Function in cell B8, using B3 as the Year Argument and Month Argument. In the third argument, instead, of “DAY(B3)” put 1.
This procedure will oblige the function return the 1th day, not the 25th.
Objective achieved:
Finding the Last Day of Any Month
Some occasions require the calculation of the last day of a month, generally for financial purposes. Excel provides 2 majors paths to do it: EOMONTH Function and Date Function.
Our Goal is to calculate the Last Day of the month for a given date:
Method One: Using EOMONTH Function
The objective of this function is to return the last day of the month after or before specified amount.
The first argument is Start Date, which is the date which will be used as basis.
The second is Months. This is the amount of months before of after the Start Date that will define the period that we need to calculate the last day.
In cell B4, apply the Eomonth Function, using B3 as Start Date and 0 as Month Argument.
Remember: the Month argument need an amount of months before or after the Start Date. In this case, we are calculating the last day of the current month, therefore 0.
The function returns a number, so apply the short-cut CTRL + SHIFT + 3 in cell B3 to format this value as a date.
Method Two: Using DATE Function
The Date Function structures a date in the future or in the past by incrementing to or subtracting from any argument of the function.
The arguments are the components of a Date: years, months, days.
Generally, the function would be build similar the Figure 8. However, Excel provides the opportunity to adapt its functions to perform different objectives than the primary one.
In this case, our objective is not building up a date: it is deliver the last day of a month. For that reason, it is necessary to construct it differently.
It returns March: the next month after February
The 0 obliges the function go back to February and delivers the last day.
Apply this function in cell B7, using B3 as Year and Month Argument and 0 instead of the Day Argument.
Objective Achieved
You can learn a lot more about Date Math by watching the Tutorial “Complete Guide To Date Math” Click Here to Watch Now
