Excel-FAQ 3 -Last Day Of a Month

Finding the last date of a Month


Q: What formula can be used to get the last date for a given month and year in Excel?
Or
Q: How do I find the number of days in a month in Excel?

Answer:  This is a very common problem in Excel 2003. There is no straight forward formula to do this in 2003. I’ll discuss the formula for 2007 later in the post, first let us look at the tougher one that is 2003

Excel 2003:
There is no direct formula for this. To accomplish this we need to use a combination of few functions. Let’s look at an example:
Now suppose we have 01-Oct (or in any other date format) written in cell A1. Now in cell A2 we need to get number of days in the same month. So the formula that we use in A2 is
=DAY(DATE(YEAR(A1),MONTH(A1)+1,1)-1)  This will give you 31 as the answer. Now to get the date simply add a date function to this
Excel Last Day of month
=DATE(YEAR(A1),MONTH(A1),DAY(DATE(YEAR(A1),MONTH(A1)+1,1)-1))  will give you  31-Oct. (Remember to change the Cell format to date format)
LOGIC: The logic of the first part is very simple, I am simply going to the day 1 of the next month and subtracting 1 from that to give me the present months last date.

Excel 2007:
There is a direct formula for this called EOMONTH: Which returns the serial number for the last day of the month that is the indicated number of months before or after start_date.
 Use EOMONTH to calculate maturity dates or due dates that fall on the last day of the month.
Syntax: EOMONTH(start_date,months*)
EXAMPLE: If you have the month and year as October and 2009 respectively then we need to find the number of days or the last date of this month. So Suppose A1 has 01-Oct written it then to go to the End of month simply
= EOMONTH(A1,0)
The answer would be 31-Oct (Remember to change the Cell format to date format)
*Months= simply how many months do you want to add to the Date you have in A1. This can also be negative.
To find the number of days
=DAY(EOMONTH(A1,0))

  1. gravatar

    # by Analyst - June 6, 2010 at 10:53 PM

    This comment has been removed by the author.

  2. gravatar

    # by Analyst - June 7, 2010 at 12:01 PM

    So, you are saying =Date(year(A1),Month(A1)+1,0). Thats an easy way of doing this. Thanks for sharing this..

  3. gravatar

    # by Anonymous - June 25, 2013 at 1:11 PM

    Nice functions and very useful....

Blog Widget by LinkWithin

Search this blog..

Loading