Last day of month in Excel tutorial

This is a Microsoft Excel tutorial on how to get the last day of month in Excel. Follow this simple guide and you are good to go.




You can get Excel to find you the last day of the month with the DATE function. .

Syntax:
DATE(year;month;date)
As you can see DATE function requires the yeer, month and day as parameters. We can get these with functions YEAR() and MONTH()).

Example: first day of January 2012
DATE(YEAR(1.1.2012);MONTH(1.1.2012);1)
Note: depending on your regional settings, you might have to use commas (,) instead of semicolons (;) as a separator in the function. I have used semicolons. 

So, what is the deal for the last day of month?

If we would type 1.1.2012 in to cell A1 and we wanted to get the last day of the month to cell B2, we could type:
DATE(YEAR(A1);MONTH(A1)+1;0)
This would be in our case:
DATE(YEAR(1.1.2012);MONTH(1.1.2012)+1;0)

Explanation:

Again, DATE function requires three parameters: DATE(YEAR;MONTH;DAY). First one is pretty self explanatory in our function, but the month and the day need to be explained:

We have to add one month to the date (marked with red): (MONTH(B2)+1) so that january becomes february.

We then use zero for the last parameter (marked in red): DATE(YEAR(A1);MONTH(A1)+1;0), which forces Excel to find the last day of the previous month.

No comments:

Post a Comment