Microsoft Excel is a very popular tool used by almost all organizations across different job roles. It is used as a spreadsheet program to do numerical as well as text analysis. Microsoft Excel is a must-have skill for beginners who are looking toward a career in the Data and Analytics domain. In the interview, the interviewer ask questions related to Microsoft Excel’s inbuilt functions and their practical use cases. One of the most popular is the EOMONTH function (end of month/ last day of the month).
In this article, we will discuss the EOMONTH in detail with practical examples. Also, we will derive the Start Date of a Month from the EOMONTH function using two different approaches.
What Is EOMONTH?
EOMONTH returns the serial number for the last day of the month which is the indicated number of months before or after start_date. Use EOMONTH to calculate maturity dates or due dates that fall on the last day of the month.
Syntax of EOMONTH Function
=EOMONTH(start_date,months)Arguments/ Parameters of EOMONTH Function
The syntax of the EOMONTH has two arguments.
start_date
This is a required parameter. A date that represents the starting date. It should be entered by using the DATE function, or as a result of other formulas or functions. For example, use DATE(2010,5,21) for the 21st day of May 2010. Do not provide the start_date in text format.
If start_date is not a valid date, EOMONTH returns the #NUM! error value.
months
This is a required parameter. The number of months before or after start_date. A positive value for months yields a future date; a negative value yields a past date. It should be an integer value only otherwise truncated.
If start_date plus months yields an invalid date, EOMONTH returns the #NUM! error value.
Remarks: Microsoft Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900, is serial number 1, and January 1, 2010, is serial number 40,179 because it is 40,179 days after January 1, 1900.
Example of EOMONTH Function
Refer to the below image for examples of EOMONTH.
How To Get Start of Month From EOMONTH Function
There is no inbuilt function available in Microsoft Excel to calculate the start date of the month. To achieve this, we will use two approaches.
Method 1- Using DAY Function
In this method, we will take a valid start_date and subtract the total number of days from that start_date to get the last date of the previous month. Then in the result, we will add 1 to get the start date of the month corresponding to the given start_date.
Method 2- Using EOMONTH Function
In this method, for a valid start_date, we will find the last date of the previous month corresponding to the start_date using the EOMONTH function. Then in the result, we will add 1 to get the start date of the month corresponding to the given start_date.
Summary
In this article, we discussed Microsoft Excel’s EOMONTH function in detail with examples. In the end, we also discussed how to get the start date of a month using two methods- using DAY function and using EOMONTH function. To know more, visit Pykit.
Feel free to contact us in case of any queries.
