# Off Topic > Tips and Tutorials >  >  Number of days in month

## kvalavan

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

----------


## Paul

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.

----------

