HELP! Dates are not calculating properly!!

I feel like I'm losing my mind!
I'm trying to find the number of days in a month, but none of my formulas are responding properly.

For example,

=DAY(DATE(YEAR(2018),MONTH(2)+1,1)-1)
Should return 28

=DAY(DATE(YEAR(2018),MONTH(4)+1,1)-1)
Should return 30

=DAY(EOMONTH(A2,0)) (assuming A2 is 2)
Should return 28



NONE of these are working. They all return 31
My workbook calculations are all set to automatic, I'm not seeing any errors, my clock is set properly on the PC.
I try to evaluate the formula =DAY(DATE(YEAR(2018),MONTH(2)+1,1)-1), and this is what it says happens:


=DAY(DATE(YEAR(2018),MONTH(2)+1,1)-1)

=DAY(DATE(1905,1+1,1)-1)

=DAY(DATE(1905,2,1)-1)

=DAY(1859-1)

=DAY(1858)

=31

It makes absolutely no sense.

Thanks so much for the quick response!

I'm trying the formula you provided above:
=DAY(DATE(2018,3,1)-1), and it does give me 28, however, when I want to use variable information, it returns 31.

=DAY(DATE(A5,AZ2,1)-1)

In this case, A5 is 2018, and AZ2 is 2

Again the result is correct.

AZ2 is 2, so the formula is referring to the day before 1 Feb. If you want the end of the month, then you need AZ2+1

AHA! Perfect. Now it makes sense. Thank you so much!

Just a little FYI.

Excel stores dates as serial numbers, by counting the number of days the date falls after 0 Jan 1900 (or as a real date, 31 Dec 1899).

This means when you use a number that represents a partial date, i.e. only the month, but no day or year, that the result will most likely differ from that which you expect.

Only the DATE() function can cope with partial dates, any others require a full date (day month and year) to be certain of correct results.

For your original formula, =DAY(DATE(YEAR(2018),MONTH(2)+1,1)-1) to work, you would need to do it something like

=DAY(DATE(YEAR(A2),MONTH(A2)+1,1)-1) or =DAY(EOMONTH(A2,0))

With a proper Feb date in A2, (15 Feb 2018) in order to get the correct result of 28.