On many occasions we would like to somehow get the dates belonging to the last day of current, previous or any other month, the first day of this month or any day of the current month. This post will show you a nice trick how to do that. Whereas for the last dates of the given month the go to function in Excel would have to be EOMONTH (End Of Month), we will show a neat trick how to do this with a well-known Excel function called DATE.
First off, let’s say we would like to get the date belonging to the last day of the previous and the last day of the current month.
Now with EOMONTH that would go
Last day of the previous month =EOMONTH(TODAY(),-1)
Last day of the current month =EOMONTH(TODAY(),0)
You can fiddle with the last argument to get to any month between Jan 1900 and Dec 9999. Keep in mind, that the EOMONTH function gives you only the serial number of the date, which you then have to format as a date.
It should be said that the DATE function gives you more leeway than EOMONTH since it can return literally any day of the month you choose.
=DATE(year,month,day) gives you the date you need or better yet the serial number belonging to the date you need.
For Example =DATE(2014,10,28) gives you 12/28/2014
But here’s a kicker =DATE(2014,10,0) gives you 9/30/2014
So with DATE function, these would be the two formulas (considering our initial goal)
Last day of the previous month =DATE(YEAR(TODAY()),MONTH(TODAY()),0)
Last day of the current month =DATE(YEAR(TODAY()),MONTH(TODAY())+1,0)
Now this trick with the zero as the last argument of the date function is genius. But even better, it can even go into negative. So you can do =DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY())-2) gives you the date of two days ago.
Here are a few more examples of how you can use both functions
I can already hear you screaming what about the +1 on the month argument of the DATE function if the current month is December. Well you needless worry about that, you can easily go over 12 and the DATE function simply returns the month using the remainder after the number has been divided by 12 and even adds the number of years it get’s from the division by 12 to the year argument. Simply Brilliant and a great way towards that ever elusive Eternal Happiness.