Number of days in a month

Don C

Board Regular
Joined
Feb 24, 2002
Messages
72
The function WEEKDAY will tell me the day of the week that a date falls on.

How can I determine the number of days in the month of that date?

In cell A2, I enter a date, say 1/1/2003 and I want to know the number of days in January.

(I know, I can look on a calendar, but I'd like some sort of automation. Next year is a Leap Year and I'd like the calendar I am building to know that.)

Thanks
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Mark,

That works, but I don't BEGIN to understand why. Would you walk me through it so I know what's going on?

Thanks!
 
Upvote 0
The code above works like this:

In some cell, you have a date. Let's say it's A1. This date can be broken down into it's components (day, month, year) by using the built-in excel functions:

DAY(), YEAR(), MONTH().

Also, excel makes available the function called "DATE()" to build a date, if you have the three components. If you placed:

=DATE(2003, 1, 2)

in a cell on your worksheet, that cell would then become the date "January 2nd, 2003", depending on how you formatted it.

So, you can construct dates out of three components, using the DATE function.

This is all pulled together in the example given above, when you say:

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

You're effectively saying:

* The year is the same as the YEAR() in cell A1.

* The month is one more than the month in cell A1.

* The day is zero.

Think about that last bit. What's January 0th, 2002?

To excel, the zero-th day of a month, is the last day of the PREVIOUS month. So January 0, 2002, is actually December 31, 2001.

After all that, you surround the entire formula with the DAY() function, in order to pull out just the 2 digit day. In the case of January 0, 2002, you'll pull out the 31 from December.



I've struggled with this same issue for some time. Calculating the end of the month is something we do quite often here with our spreadsheets, so here's a function that you can place in your VB project, so that you can just type:

=EndOfMonth(A1)

In a cell, and if A1 is a date, you'll get the last day of that date.

Place this code in a new module:<pre>
Function EndOfMonth(mDate As Date) As Variant

EndOfMonth = Day(DateSerial(Year(mDate), Month(mDate) + 1, 0))

End Function</pre>

_________________<form action="http://www.google.com/search" name=f>
google.gif
<input style="border:1px solid;" maxLength=256 size=15 name=q value=""><input type=submit value="Search" name=btnG></form>
This message was edited by OdinsDream on 2003-01-06 14:09
 
Upvote 0
The DATE worksheet function returns the datevalue for a given set of arguments that include year, month, day. If day is 0 or omitted DATE returns the datevalue for the last day of the preceeding month. So, I added 1 to the month and omitted the 3rd argument to get the datevalue for 1/31/2002. The DAY function converts a datevalue to an integer between 1 and 31 representing the day portion of the date.
This message was edited by Mark W. on 2003-01-06 14:11
 
Upvote 0
There's no reason to write VBA code for a EOM function since this function already exists in the Analysis ToolPak...

=DAY(EOMONTH(A2,0))

I recommended DAY(DATE(...)) only to avoid the use of thec Analysis ToolPak!
This message was edited by Mark W. on 2003-01-06 14:15
 
Upvote 0
Well, everybody has their own preference. Since our workbooks are distributed to many different machines, we can't control whether the Analysis ToolPak is installed or not.

We also calculate the End-of-Month within many VB macros in the workbooks, so it's just easier to call the function.

to each his own.
 
Upvote 0
...which is why I recommended...

=DAY(DATE(YEAR(A2),MONTH(A2)+1,))

If one would like to hide the complexity of this formulation... It's easy enough to create a defined name, EndOfMonth, that refers to =DAY(DATE(YEAR(A2),MONTH(A2)+1,)).
This message was edited by Mark W. on 2003-01-06 14:42
 
Upvote 0
Thanks for the explanation. My thought process was working on a max function and getting nowhere!

The date of zero taking you back a month was the critical missing information, although the truth is I'd have been hard pressed to USE that information.

Thanks for the formula AND the lesson!
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,184
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top