Thread: formula for days in a month (excel 2000)

1. formula for days in a month (excel 2000)

Is there a formula that will yield the number of days in a month, with a single date as input?

Thank you

2. Re: formula for days in a month (excel 2000)

This will yield the number of days in the month of the date given in A1:

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

Steve

3. Re: formula for days in a month (excel 2000)

Excellent!!

Know any www site that has related tips on similar subject (excel & dates)?

Thank you.

4. Re: formula for days in a month (excel 2000)

Chip Pearson has a site with a lot of useful Excel topics and tips

Here is the part on date and time:
http://www.cpearson.com/excel/datetime.htm

Steve

5. Re: formula for days in a month (excel 2000)

Guillermo, you mean you don't like the Lounge? <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

An excellent site for many Excel topics is (Chip) Pearson Software Consulting, and you'll find several time and date sections within that site in his topic listing.

6. Re: formula for days in a month (excel 2000)

Lounge is GREAT!!!

The Lounge beats any source of information about excel and other (several) products. I feel bad for posting questions that are very basic. I should study more!

Thanks!

7. Re: formula for days in a month (excel 2000)

The only bad question here is one that you don't ask. If you need to ask, then there are probably a lot of others out there that don't know either.

8. Re: formula for days in a month (excel 2000)

Hi Steve,

A marginally shorter formula:
=DAY(DATE(YEAR(A1),MONTH(A1)+1,0))

Cheers

9. Re: formula for days in a month (excel 2000)

Or shorter still
=DAY(EOMONTH(A1,0))

But you need the analysis toolpak addin.

10. Re: formula for days in a month (excel 2000)

Hi Andrew,

Good call. As you say, you need the analysis toolpak addin for your solution. I tend to avoid solutions involving add-ins since I can't rely on users having them installed. If that happens, the formula from the add-in ceases to work and the spreadsheet can no longer be relied on to calculate correctly.

Cheers

11. Re: formula for days in a month (excel 2000)

I also avoided EOmonth for this reason.

I also avoided the day being "0". I have seen some squirrley results with it sometimes, so I try to stick with acceptable values and not use trickery to fool the function.

Steve

12. Re: formula for days in a month (excel 2000)

Hi Steve,

I often use the equivalent of =DAY(DATE(YEAR(A1),MONTH(A1)+1,0)) in Access, and I have never experienced a problem with it.

If you don't trust such tricks, you should also avoid =DAY(DATE(YEAR(A1),MONTH(A1)+1,1)-1), for if the date in A1 is in December, MONTH(A1)+1 evaluates to 13. The 13th month of the year seems just as dangerous (or safe) as the 0th day of the month.

13. Re: formula for days in a month (excel 2000)

You are right. I just didn't think about the 13th month ("brain fart") or I would have made a more complicated formula. Who wants to be consistent anyway in how we do things?

I know it works MOST of the time. I have just seen some cases when it had problems (usually with imported spreadsheets).

Steve

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•