A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

# of days in month



 
 
Thread Tools Display Modes
  #1  
Old May 2nd, 2007, 09:02 PM posted to microsoft.public.excel.worksheet.functions
dipsy
external usenet poster
 
Posts: 5
Default # of days in month

I know the month and want a formula that will tell me number of days in the
month. For example, April would be 30 days and May would be 31 days.
  #2  
Old May 2nd, 2007, 09:21 PM posted to microsoft.public.excel.worksheet.functions
PCLIVE
external usenet poster
 
Posts: 1,024
Default # of days in month

Where A1 contains a date:

=DAY(DATE(YEAR(A1),MONTH(A1)+1,0))


"dipsy" wrote in message
...
I know the month and want a formula that will tell me number of days in the
month. For example, April would be 30 days and May would be 31 days.



  #3  
Old May 2nd, 2007, 09:21 PM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,759
Default # of days in month

If you have a date:

A1 = some date like 4/12/2007 (April 12 2007)

=DAY(DATE(YEAR(A1),MONTH(A1)+1,0))

Returns 30

Biff

"dipsy" wrote in message
...
I know the month and want a formula that will tell me number of days in the
month. For example, April would be 30 days and May would be 31 days.



  #4  
Old May 2nd, 2007, 09:29 PM posted to microsoft.public.excel.worksheet.functions
Peo Sjoblom
external usenet poster
 
Posts: 3,232
Default # of days in month

=DAY(DATE(YEAR(A1),MONTH(A1)+1,0))

with a date in A1 and the cell with the above formatted as general it will
do what you want.

--
Regards,

Peo Sjoblom



"dipsy" wrote in message
...
I know the month and want a formula that will tell me number of days in the
month. For example, April would be 30 days and May would be 31 days.



  #5  
Old May 2nd, 2007, 09:48 PM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,759
Default # of days in month

If all you have is the month name as a TEXT entry:

A1 = April or Apr

=DAY(DATE(YEAR(A1&1),MONTH(A1&1)+1,0))

Returns the number of days for the month *of the current year*. Where that
might be important is in a leap year.

Biff

"T. Valko" wrote in message
...
If you have a date:

A1 = some date like 4/12/2007 (April 12 2007)

=DAY(DATE(YEAR(A1),MONTH(A1)+1,0))

Returns 30

Biff

"dipsy" wrote in message
...
I know the month and want a formula that will tell me number of days in
the
month. For example, April would be 30 days and May would be 31 days.





  #6  
Old May 4th, 2007, 03:19 PM posted to microsoft.public.excel.worksheet.functions
dipsy
external usenet poster
 
Posts: 5
Default # of days in month

Thanks! It worked - I wanted to know how it works. I broke up the formula
and get the parts. When I put it together - DATE(YEAR(A1),MONTH(A1)+1,0) - I
get the date - 7/31/2001 and then day (7/31/2001) - 31. Why add 1 to the
month?

Thanks a ton!

"PCLIVE" wrote:

Where A1 contains a date:

=DAY(DATE(YEAR(A1),MONTH(A1)+1,0))


"dipsy" wrote in message
...
I know the month and want a formula that will tell me number of days in the
month. For example, April would be 30 days and May would be 31 days.




  #7  
Old May 4th, 2007, 04:13 PM posted to microsoft.public.excel.worksheet.functions
PCLIVE
external usenet poster
 
Posts: 1,024
Default # of days in month

This works because of the DATE command. If you used this formula:
=DATE(2007,5,1)

The result is 5/1/2007.

If you used:
=DATE(2007,5,32)
Since you are using the DATE command and Excel knows that May 32nd, 2007 is
not a valid date, it moves to the next month and displays:
6/01/2007

The same thing applies if you use:
=DATE(2007,5,0)
Again, May 0,2007 is not a valid date...so Excel moves backward to the
previous month by one day, which will be the last day of the previous month
specified (in this case, 5 or May. So by adding 1 to the current month, we
get next month...and by finding day 0 of next month, we get the last day of
THIS month.

Hope this helps,
Paul


"dipsy" wrote in message
...
Thanks! It worked - I wanted to know how it works. I broke up the
formula
and get the parts. When I put it together -
DATE(YEAR(A1),MONTH(A1)+1,0) - I
get the date - 7/31/2001 and then day (7/31/2001) - 31. Why add 1 to the
month?

Thanks a ton!

"PCLIVE" wrote:

Where A1 contains a date:

=DAY(DATE(YEAR(A1),MONTH(A1)+1,0))


"dipsy" wrote in message
...
I know the month and want a formula that will tell me number of days in
the
month. For example, April would be 30 days and May would be 31 days.






  #8  
Old July 15th, 2007, 09:45 PM posted to microsoft.public.excel.worksheet.functions
Dave Thomas
external usenet poster
 
Posts: 146
Default # of days in month

You can also use the following. If A1 contains a date, then the number of
days in the month represented by the date is given by: =DAY(EOMONTH(A1,0))
In this example the EOMONTH function adds 0 to the month of the date in A1
and calculates the date of the last day of the month. Then the DAY function
returns the day number (1-31) of that date.
Note: You must have the Analysis Toolpack add-in installed in versions prior
to Excel 2007 to use the EOMONTH function.


"dipsy" wrote in message
...
I know the month and want a formula that will tell me number of days in the
month. For example, April would be 30 days and May would be 31 days.



  #9  
Old July 15th, 2007, 10:31 PM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,759
Default # of days in month

Returns the number of days in a month for the current year.

If all you have is the month name (as a TEXT entry) in either long or short
form:

A1 = July or Jul

=DAY(DATE(YEAR(A1&1),MONTH(A1&1)+1,0))

If all you have is the month number:

A1 = 7

=DAY(DATE(YEAR(TODAY()),A1+1,0))

If you have any date for that month (with a specific year):

A1 = 7/22/2007

=DAY(DATE(YEAR(A1),MONTH(A1)+1,0))

--
Biff
Microsoft Excel MVP


"Dave Thomas" wrote in message
news
You can also use the following. If A1 contains a date, then the number of
days in the month represented by the date is given by:
=DAY(EOMONTH(A1,0)) In this example the EOMONTH function adds 0 to the
month of the date in A1 and calculates the date of the last day of the
month. Then the DAY function returns the day number (1-31) of that date.
Note: You must have the Analysis Toolpack add-in installed in versions
prior to Excel 2007 to use the EOMONTH function.


"dipsy" wrote in message
...
I know the month and want a formula that will tell me number of days in
the
month. For example, April would be 30 days and May would be 31 days.





 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:55 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.