Two functions exist in Excel that can assist you in changing the names of months to numbers of months. They are the DATAVALUE and the MONTH function. The function of the DATAVALUE is changing a date saved as a text into a serial number which is recognizable by Microsoft Excel as a date. In comparison, the function of the Month is to pull out the number of a month from that date converted or changed.
The formula for this is:
=MONTH(DATEVALUE(A2 & "1"))
Where
A2 is a cell that contains the name of the Month you intend to change to a number, and 1 is attached to the function, i.e., DATAVALUE function, in order to show that it is a date.
The EOMONTH function (how to obtain the last day of the Month in Excel)
By making use of this function, the last day of the Month is being returned based on a particular starting date. It has two functions that are both necessary for workability:
EOMONTH(start_date, months)
Start_date – the starting date or a reference to a cell with the start date.
Months – the number of months before or after the start date. Use a positive value for future dates and a negative value for past dates.
Below are some formula workings on the EOMONTH function:
=EOMONTH(A2, 1) – returns the last day of the Month, one Month after the date in cell A2.
=EOMONTH(A2, -1) – returns the last day of the Month, one Month before the date in cell A2.
Apart from making reference to a cell while making use of the EOMONTH function, you can also hardcode a date. For instance, the last day in the Month of April is being returned in the two examples below.
=EOMONTH("15-Apr-2015", 0)
=EOMONTH(DATE(2015,4,15), 0)
The last day of the current Month can be returned by making use of the TODAY () function present in the first argument while operating on your EOMONTH Formula. The aim of this is to make today's date the starting date. After that, you insert 0 in the space created for the Month in order not to change the Month.
=EOMONTH(TODAY(), 0)
Note. As already stated, the serial number showing or carrying the date is being returned by the EOMONTH function; the date format to a cell(s) must be used with your Formula. Further inquiry on How to Change the date format in Excel Worksheet might be more helpful.
Below are results returned by the Formula talked about above.
Formulas present in EOMONTH function to return the last day of the Month
You minus or take away the date returned by TODAY () from the date returned by EOMONTH and use the General format to a cell in order to ascertain the number of days left in a month.
=EOMONTH(TODAY(), 0)-TODAY()