# Count Days in a Month

#### Vanda_a

##### Well-known Member
Dear all

Could someone explain below formula to me please.
Rich (BB code):
``=DAY(DATE(YEAR(A1),MONTH(A1)+1,1)-1)``
The red parts, no idea.

Thank you very much

### Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Dear all

Could someone explain below formula to me please.
Rich (BB code):
``=DAY(DATE(YEAR(A1),MONTH(A1)+1,1)-1)``
The red parts, no idea.

Thank you very much

1. Given a date, the formula constructs first a first day date following the month/year of A1.

A1 = 4/20/2015

==> 5/1/2015

Note that this is DATE applied to 2015, 5 (MONTH of A1, which is 4, plus 1) and 1.

2. Subtracting 1 from the first day date of (1), that is,

5/1/2015 minus 1, we get:

==> 4/30/2015

3) DAY applied to (2), we get:

==> 30

which is days April (of 2015) counts.

1. Given a date, the formula constructs first a first day date following the month/year of A1.

A1 = 4/20/2015

==> 5/1/2015

Note that this is DATE applied to 2015, 5 (MONTH of A1, which is 4, plus 1) and 1.

2. Subtracting 1 from the first day date of (1), that is,

5/1/2015 minus 1, we get:

==> 4/30/2015

3) DAY applied to (2), we get:

==> 30

which is days April (of 2015) counts.

Understand it now. Thank you very much.

Vanda_a, Good morning.

I suggest you to use this formula:

=DAY(EOMONTH(A1),0)

I think it's more simple.

I hope it helps.
Have a nice day.

Understand it now. Thank you very much.

You are welcome.

Since EOMONTH is directly available in the recent Excel versions...

=DAY(EOMONTH(A1,0))

can also be invoked to the same effect.

Alternative to last day of previous month from today:

=DAY(TODAY()-DAY(TODAY()))

or a date in A1

=DAY(A1-DAY(A1))

You are welcome.

Since EOMONTH is directly available in the recent Excel versions...

=DAY(EOMONTH(A1,0))

can also be invoked to the same effect.

Yeah... This one is alot easier.

Thanks again

Vanda_a, Good morning.

I suggest you to use this formula:

=DAY(EOMONTH(A1),0)

I think it's more simple.

I hope it helps.
Have a nice day.

Thanks

Alternative to last day of previous month from today:

=DAY(TODAY()-DAY(TODAY()))

or a date in A1

=DAY(A1-DAY(A1))

Thanks

Replies
3
Views
197
Replies
7
Views
175
Replies
4
Views
409
Replies
2
Views
144
Replies
5
Views
124

1,203,483
Messages
6,055,678
Members
444,807
Latest member
RustyExcel

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back