Number of days in month

This formula helps you to calculate the number of days in any given month.

Let us take an example, you have entered the months ‘Feb 12’, ‘Mar12’, ‘Apr 12’ in cells A3 to A5. Enter this following formula in Cells B3 to B5

=DAY(DATE(YEAR(A1),MONTH(A1)+1,1)-1)

Let us analyze how does this formula works;

Definitions:

Day : Returns the day of the month, a number from 1 to 31
Date : Returns the number that represents the date in the MS Excel date-time code
Year : Returns the year of a date, an integer in the range 1900 – 9999
Month : Returns the month, a number from 1 (January) to 12 (December)

The logic : one day minus the first day of the succeeding month will give the desired result.

When we applied the formua Month(A1)+1, Excel returns the month in the cell A3 in number as 2 and when added 1 to this resulted in 3, which represents the succeeding month.

Similarly, when the formula Year(A3) was applied, it returns the year in number as 2012

X month
By adding 1 to month, month is pushed to the succeeding month
Year is calculated
Date : When selected the year, succeeding month and the 1st date, it turns the excel value of the 1st day of the succeeding month
Day : When 'Day' function is applied with 1st day of the succeeding month less 1, (i.e., the last day of the month), it gives the last day of the month

You could also use either of the following formulas:

=DAY(DATE(YEAR(A1),MONTH(A1)+1,0))

=DAY(EOMONTH(A1,0))

To use EOMONTH prior to Excel 2007 you must enable the Analysis Toolpak add-in.