Last Day of Month in Excel
December 15th, 2004
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 couldn’t find. 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!
Well done old chap.
Just love the web. You done this in 2004 and I needed it now.
Thanks again.
Great help!! Thank you.
Thanks!!!
Actually, Excel DOES offer the EOMONTH function to do get the last day of the month, but it’s not part of the main set of functions. You have to activate the Analysis Toolpack (under Tools->Add-ins) to get it.
Of course, if you’re sharing your spreadsheet with other users, THEY have to make sure they have it enabled, too. Otherwise, all those nice dates you see on your screen turn into #NAME? errors on theirs. I’ve been having to deal with this at my office recently (today, in fact). I think I might just switch to your solution instead.
much useful.
Thanks so much.
Valdemar
That helped me impress my peers – three cheers for you !! :)
PaNK CoOL
Plus one to thank you.
excellent information. Many more thanks for this.
Freaking brilliant.
I used this for a sales calculation to mark revenue as ‘in month’ if the projected close was less than the last day of the month.
How do you calculate the last business day of the month????
I think you just saved me an entire weekend!
thanks a lot :-)
You are a rockstar! Thanks!
@Louis Lodder
HERE HERE. I us3ed it again in 2009!