OT - MS Excel - # days in month

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

Reply to
Laurie Forbes
Loading thread data ...

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

Reply to
Dave Keith

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

Reply to
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

Reply to
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>>
Reply to
David Billington

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-

Reply to
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

Reply to
Rich

Thanks Cliff:

I haven't tried it yet but it looks like it would handle leap years as well.

Laurie Forbes

Reply to
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

Reply to
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.

Reply to
Trevor Alvey

There's some very intelligent Excel users over at

formatting link
and especially in the message boards,
formatting link
. I've gotten help to lots of tough questions on macros and such over there.

Reply to
bpryor

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.