# total days in the month based on date

#### bark01

##### Board Regular
Hi, is there a formula that will say how many days there are in a month ?

B9 contains the a refrence to the first day of the month eg 1/7/2008 I want the cell above it (B8) to return 31. If the date reference is 1/6/2008 I want (B8) to return 30.

I'm using it to pro rata by the number of days in a month.

regards

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

#### njimack

##### Well-known Member
If you have the analysis toolpak installed, use:
PHP:
``=DAY(EOMONTH(B9,0))``

If not, use:
PHP:
``=DAY(DATE(YEAR(B9),MONTH(B9)+1,0))``

or:
PHP:
``=TEXT(DATE(YEAR(B9),MONTH(B9)+1,0),"dd")``

EDIT: For pro-rata calculations, you may also want to take a look at the NETWORKDAYS function, which calculates the number of working days in a month. This requires the analysis toolpak.

#### DonkeyOte

##### MrExcel MVP
removed - use Neil's solution.

Last edited:

#### barry houdini

##### MrExcel MVP
Here's another way.....

=DAY(B9+32-DAY(B9+32))

#### Richard Schollar

##### MrExcel MVP
Barry

I thought the magic number was 42

#### barry houdini

##### MrExcel MVP
I thought the magic number was 42

Hello Richard,

Because of the "credit crunch", 42 has proved too expensive, I've had to economise and use just 32.......

#### mortgageman

##### Well-known Member
Barry

I thought the magic number was 42

I'm still not understanding why all numbers larger than 31 do not work. (And they don't - 99 and 100 among others give incorrect results)

#### bark01

##### Board Regular
thanks for all the quick responses. My sheet will be used by a lot of people so I can't use the tools packs as most people won't have them installed. I'll try the other options

thanks again

#### barry houdini

##### MrExcel MVP
I'm still not understanding why all numbers larger than 31 do not work. (And they don't - 99 and 100 among others give incorrect results)

Assuming B9 is 1st of the month [which is what bark01 says] it can be any number that, when added to B9, will give a date in the next month, any number from 31 to 58 I believe.

In fact, if B9 is always 1st of the month you only need

=32-DAY(B9+31)

again, you can use any number from 31 to 58 for the second number, with the first 1 higher.

If B9 is any date this formula gives the number of days in the month

=42-DAY(B9-DAY(B9)+42)

#### mortgageman

##### Well-known Member
Assuming B9 is 1st of the month [which is what bark01 says] it can be any number that, when added to B9, will give a date in the next month, any number from 31 to 58 I believe.

In fact, if B9 is always 1st of the month you only need

=32-DAY(B9+31)

again, you can use any number from 31 to 58 for the second number, with the first 1 higher.

If B9 is any date this formula gives the number of days in the month

=42-DAY(B9-DAY(B9)+42)

I see. The game is to get into the next month and only the next month. Got it now.

Replies
4
Views
162
Replies
5
Views
145
Replies
7
Views
263
Replies
3
Views
241
Replies
9
Views
372

1,190,794
Messages
5,982,960
Members
439,808
Latest member
agutosay

### 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.

### Which adblocker are you using?

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

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