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.

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...
ReplyDelete=A1-DAY(A1)+32-DAY(A1-DAY(A1)+32)
Hi Rick
ReplyDeleteThis 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.
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...
ReplyDeleteexcel vba courses