I cannot figure out how to get Excel to return the number of days in a given month (not necessarily the current month) - would be much appreciative if someone could describe a method (if one exists).
TIA for any assistance...........
Laurie Forbes
I cannot figure out how to get Excel to return the number of days in a given month (not necessarily the current month) - would be much appreciative if someone could describe a method (if one exists).
TIA for any assistance...........
Laurie Forbes
How about this function (from Excel help file) :
DATEDIF
Calculates the number of days, months, or years between two dates. This function is provided for compatibility with Lotus 1-2-3.
Syntax
DATEDIF(start_date,end_date,unit)
Start_date is a date that represents the first, or starting, date of the period. Dates may be entered as text strings within quotation marks (for example, "2001/1/30"), as serial numbers (for example, 36921, which represents January 30, 2001, if you're using the 1900 date system), or as the results of other formulas or functions (for example, DATEVALUE("2001/1/30")). For more information about date serial numbers, see NOW.
Dave
I chose CHOOSE Trev and it works fine. I will need another selection to handle leap year but I think I can figure that out.
Thanks much for the help - one can get assistance on just about anything on this group!
Laurie Forbes
Hi Dave:
I'm not sure the above will work for me as I don't know how to get the end day (or date) of the month in question. If I did, that would itself supply the answer.
In any case, Trev has kindly supplied some alternatives, the
=CHOOSE(MONTH(D7),31,28,31,30,31,30,31,31,30,31,30,31)
function appearing to be the most straighforward for my purposes.
If I'm wrong in my interpretation of your response, please let me know.........
Laurie Forbes
Don't know if excel has a leap year function, if it does great. If not a leap year is a year divisible by 4 but not by 100 except when divisible by 400. The C code is
if (year % 4 == 0) && ((year % 100) > 0) || (year % 400 == 0))) its a leap year
This said unless you are doing historical data or planning on it being used for a long time the usual divisible by 4 should suffice now that
2000 has g>>Laurie,
Use the Date() function...
Syntax: Date(year, month, day)
But trick it and use 0 for the day...this will return the last day of the previous month (this works across year boundaries as well, I.e. Date(2003,1,0) = 12/31/2002)
If you want the number of days, use Day(Date(2003,1,0)) = 31
-cliff knight-
Here's the easiest way to do it. This example assumes that you want to know how many days are February, 2003.
=DATEVALUE("3/1/2003") - DATEVALUE("2/1/2003")
Basically, just subtract day one of the month in question from day one of the next month. Leap year is accounted for by this method. The DateValue function just changes the text date into a numeric. If you are dealing with actual dates rather then their text representaion just subtract the dates without using the DateValue function.
----- Original Message ----- From: "Laurie Forbes" Newsgroups: rec.crafts.metalworking Sent: Monday, December 01, 2003 1:31 PM Subject: OT - MS Excel - # days in month
Thanks Cliff:
I haven't tried it yet but it looks like it would handle leap years as well.
Laurie Forbes
Thanks Rich:
With the above method, it would be necessary to calculate the previous month/year (so the two could be subtracted). Here's what may be an even easier method that was suggested on the microsoft.public.excel group.
=DAY(DATE(YEAR(A1), MONTH(A1)+1, 0))
where the date in question is stored in A1. The "0" day fools the function into giving the last day of the previous month. So, "1' is added to MONTH to get a result for the correct month. This method too handles leap years properly. I was a bit surprised to find it also handles December properly (where MONTH+1 would = 13).
Laurie Forbes
This should work if you want month/year lookup. There are two alternatives, depending on regional date settings. If you use US-style dates try
=IF(TYPE(DATEVALUE(MONTH(D7)&"-31-"&YEAR(D7)))=1,31,IF(TYPE(DATEVALUE(MONTH( D7)&"-30-"&YEAR(D7)))=1,30,IF(TYPE(DATEVALUE(MONTH(D7)&"-29-"&YEAR(D7)))=1,2
9,28)))with the month/year in cell D7 (this works by succesively trying 31,30,29 days in the month to see if excel gives an error)
If you use normal dates try
=IF(TYPE(DATEVALUE("31-"&MONTH(D7)&"-"&YEAR(D7)))=1,31,IF(TYPE(DATEVALUE("30
-"&MONTH(D7)&"-"&YEAR(D7)))=1,30,IF(TYPE(DATEVALUE("29-"&MONTH(D7)&"-"&YEAR( D7)))=1,29,28)))
with the month/year in cell D7 If you want to fix the year replace the &YEAR(D7) with the year, eg &"2003"
If you just want a simple lookup list then try this
=CHOOSE(MONTH(D7),31,28,31,30,31,30,31,31,30,31,30,31)
Trev.
There's some very intelligent Excel users over at
PolyTech Forum website is not affiliated with any of the manufacturers or service providers discussed here. All logos and trade names are the property of their respective owners.