Saturday, January 24, 2009

Entering last day of the month in Excel

Entering a specific date in excel is easy as you know what to enter, however if you are asked to enter last date of the month it becomes little tricky as last date of the month varies between 28 to 31 depending on what year and month it is.

However Excel date mathematics makes it easy. What you need to do is enter the first day of next month and then reduce it by 1, you will get last day of previous month.

Look at following picture to understand it better.


I have used date function to enter Feb 1, 2009 and then reduced the day by 1, other wise you can enter =Date(2009,2,0) also to get same result. after you have added above formula in Cell B4 you can use this formula to enter last day of next months in cell B5 =DATE(YEAR(B4),MONTH(B4)+2,0). You can further copy this down to get next month.

Posted by Yogesh Gupta at 7:09 PM
Labels:
Reactions: 

3 comments:

  1. Just thought you might find this completely different method of finding the last day of the month for a given date interesting. If A1 contains a date, then this formula gives the date for the last day of its month...

    =A1-DAY(A1)+32-DAY(A1-DAY(A1)+32)

    ReplyDelete

  2. Hi Rick

    This is completely innovative way to
    Reduce the days and bring the date to last day of previous month. Then add 32 days and reduce the day count of the next month,

    This calculates to the date last day of the current month.

    Thanks for your inputs.

    ReplyDelete

  3. This is such a great resource that you are providing and you give it away for free. I love seeing blog that understand the value. Im glad to have found this post as its such an interesting one! I am always on the lookout for quality posts and articles so i suppose im lucky to have found this! I hope you will be adding more in the future...
    excel vba courses

    ReplyDelete

Subscribe to: Post Comments (Atom)