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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
# 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
|
|||
|
|||
# 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
|
|||
|
|||
# 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
|
|||
|
|||
# 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
|
|||
|
|||
# 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
|
|||
|
|||
# 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
|
|||
|
|||
# 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
|
|||
|
|||
# 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
|
|||
|
|||
# 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 | |
|
|