Days in Month Question

[Joe]

I am trying to create a formula that uses the number of days in a
month. For example, I put the date 07/31/2003 in cell "A1" and I want
to create a formula that will return the number of days in cell "A2"
It is important that I be able to enter any date in cell "A1" and have
the days value returned in cell "A2." I have tried multiple ways and
have had no luck. Any help out there would be appreciated.

 

[JulieD]

Hi Joe

if i understand you correctly the following should work

=DAY(A1)

Cheers
JulieD

 

[CLR]

=VLOOKUP(MONTH(A1),daystable,2,FALSE)

and create a VLOOKUP table named DaysTable that contains 1-12 down one
column and the number of days in each month in the corresponding column just
to the right.........

Vaya con Dios,
Chuck, CABGx3

 

[Dave Peterson]

You want the number of days in the month--not the number of days into the month
for that date:

=day(date(year(a1),month(a1)+1,0))

The zeroeth day of a month is the last day of the previous month.

 

[JMay]

Dave:
any particular reason you enclosed
=date(year(a1),month(a1)+1,0) <<< which works!
inside of day() ?
TIA,

 

[Norman Harker]

Hi JMay!

It depends what you want:

=DATE(YEAR(A1),MONTH(A1)+1,0)
Returns the date of the last day in the month
You could format that d or dd to get the number shown but the
underlying figure saved is a date serial number.

=DAY(DATE(YEAR(A1),MONTH(A1)+1,0))
Returns the number of days in the month.

If I wanted to apply $100 per day based on the number of days in the
month, I'd use the second option.

=DAY(DATE(YEAR(A1),MONTH(A1)+1,0))*100
Returns: 3100

If I used:
=DATE(YEAR(A1),MONTH(A1)+1,0)*100
Returns: 3807700



--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

Dave:
any particular reason you enclosed
=date(year(a1),month(a1)+1,0) <<< which works!
inside of day() ?
TIA,

You want the number of days in the month--not the number of days

into the
month

 

[JMay]

Clear as a bell!!
Thanks Norman,

Hi JMay!

It depends what you want:

=DATE(YEAR(A1),MONTH(A1)+1,0)
Returns the date of the last day in the month
You could format that d or dd to get the number shown but the
underlying figure saved is a date serial number.

=DAY(DATE(YEAR(A1),MONTH(A1)+1,0))
Returns the number of days in the month.

If I wanted to apply $100 per day based on the number of days in the
month, I'd use the second option.

=DAY(DATE(YEAR(A1),MONTH(A1)+1,0))*100
Returns: 3100

If I used:
=DATE(YEAR(A1),MONTH(A1)+1,0)*100
Returns: 3807700



--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Norman Harker]

Hi JMay!

Never hesitate to ask for reasons! You'll get a lot more out of the
newsgroups that way.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[JMay]

I totally agree Norman;
thanks for the encouragement to do so.
JMay