How to calculate days in a month using Excel formula ?

Below is the formula which can be used to calculate the days in a specific month.

Enter any date in cell B2

Copy paste the below formula in Cell B3

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


e.g. if the value in the Cell B2 is 10/22/2014 (i.e. 22nd october 2014)

Then the result in the cell B3 will be 31


How it works ?

we will break up the above formula in  parts.

1. Year(B2)          - this will provide us the year of the date result (2014) 
2. Month(2) + 1   - this will provide us the next months number result (11)
3. 1                     - one is entered to get 1st date of next month 
4. -1                     - this minus one will give us last day of previous month result (10/31/2014)
5. Day                 - this formula will provide us the required day output result (31)

conclusion - Initially we are finding first day of next month, then subtracting it with 1 to get last day of current month and at the end with the help of day formula we get the last day i.e. 31 as output.

Done...