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.