Last Day of Month in Excel
December 15th, 2004
24 comments
My next Excel Top Tip is how to calculate the last day of a month. I thought there was a function to do this as part of a whole suite of date manipulation functions, but I seem to have imagined that, as I can’t find it now. I was dreading having to do lots of nasty date arithmetic, but then I discovered that the zeroth day of a month is treated as the last day of the previous month! So the last day of the month for the date that’s in cell B3 is simply:
=DATE(YEAR(B3), MONTH(B3)+1, 0)
It even works across year boundaries, so DATE(2004, 13, 0) really does give December 31st 2004!
