## 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.

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)

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.