total days in the month based on date

bark01

Board Regular
Joined
Sep 6, 2006
Messages
135
Office Version
  1. 365
Platform
  1. Windows
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

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
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.
 
Upvote 0
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
 
Upvote 0
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)
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,089
Members
448,548
Latest member
harryls

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?

Disable AdBlock

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
Back
Top