Is there a function that returns the number of days in a month? 1

[Apollo6]

I have an Excel formula that uses the number of days in a month. Basically, the formula uses the sum of the past three months worth of days and divides into a dollar amount. Is there a function that is built into Microsoft that can return the number of days in a month? I would like to avoid creating a reference section on the worksheet.

Else, I would be up to any suggestions as to how to return this count.

Thanks

 

[xlbo]

This may get you started:
=EOMONTH(VALUE("01/"&MONTH(NOW())&"/"&YEAR(NOW())),0)-VALUE("01/" & MONTH(NOW())-2 &"/" &YEAR(NOW()))+1

It works out how many days there are between the end of the current month and the start of 2 months previous (ie 3 months)
so, for June, July and August, you get 92 (30+31+31)
when we get into september, it'll still show 92 (31+31+30)

HTH Rgds
~Geoff~

 

[acron]

If you have the analysis toolpak installed and active, the

=DAY(EOMONTH(A1,0))

should work for a date in A1

To included the toolpak, goto Options, AddIns and check Analysis Toolpak.

A.C.

 

[Loomah]

I hate dates! But here's a couple of variations on a theme

Number of days in last 3 months from TODAY ie from 22 May
=TODAY()-EDATE(TODAY(),-3)

Number of days in 3 months upto the end of last month =EOMONTH(TODAY(),-1)-EDATE(EOMONTH(TODAY(),-1),-3)

Might have to check them, both 92.

Both require Analysis Toolpak Add-In to be installed

;-) If a man says something and there are no women there to hear him, is he still wrong?

 

[DaleWatson123321]

Hi Apollo,

Yet another option...

cell A1 - enter label: curyear
cell B1 - enter value: 2002 (example - could be any year)

cell A2 - enter label: endmth
cell B2 - enter value: 2 (example - could be any month)

cell A3 - enter label: endmthday
cell B3 - enter formula: =DATE(B1,B2,28)

cell A4 - enter label: less 80days
cell B4 - enter formula: =B3-80

cell A5 - enter label: startmth
cell B5 - enter formula: =MONTH(B4)

cell A6 - enter label: revyear
cell B6 - enter formula: =IF(OR(B5=12,B5=11),B1-1,B1)

cell A7 - enter label: startdate
cell B7 - enter formula: =DATE(B6,B5,1)

cell A8- enter label: enddate
cell B8 - enter formula: =DATE(B1,B2+1,1)-1

cell A9 - enter label: daycount
cell B9 - enter formula: =B8-B7+1

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[acron]

For a 3 month period use the following approach.

This give the number of days for the three past months, e.g used today it returns the sum of the days in May, Jun and July. you can change the second parameter in each case to a number less to get the three months including current month.

=DAY(EOMONTH(TODAY(),-1))+DAY(EOMONTH(TODAY(),-2))+DAY(EOMONTH(TODAY(),-3))

A.C

 

[JohnYingling]

Since this is the VBA forum I will assume you can use a VBA function.

Day Function
Returns a Variant (Integer) specifying a whole number between 1 and 31, inclusive, representing the day of the month.
Syntax
Day(date)
The required date argument is any Variant, numeric expression, string expression, or any combination, that can represent a date. If date contains Null, Null is returned.

http://www.VBResizer.com

Forms/Controls Resizing/Tabbing Control

http://www.VBCompare.com

Compare Code (Text)

http://www.VBSortGen.com

Generate Sort Class in VB or VBScript

 

[Apollo6]

I've been out the past week, but thanks for all the suggestions. Currently, I put input boxes in that require the user to specify the beginning and ending date range, then use the day count later in the code. Idealy, I don't want to involve the user in the process. If it can be a formula, all the better. Thanks again!