How to get first day of the month

Using the DATE function

=DATE(YEAR(TODAY()), MONTH(TODAY()), 1)

You can replace TODAY() to get first day of any date

How to use DATE function

The DATE function seems "not so useful" at first sight, enter the year, month, day numbers to get a date. Why would you want to do that in a function when you can just enter the date text directly into the cell, saves time and more human.

Well the DATE function has more uses than you think, it serializes date. Try entering erroneous values into the inputs. For example the 31st day in September, or 13 as month.

I show you what happens if you do that:

=DATE(2011, 9, 31) returns 1 October, 2011

=DATE(2012, 13,4) returns 4 January, 2013

Our first line,  31st Sept 2011 will be serialized to the next day of 30 Sept 2011, since there's only 30 days in Sept, which is 1st Oct 211.

Our second line will be 4th of imaginary month of December plus one 2012, 4th Jan 2013 will be return. The next year date, the next month after Dec will be Jan.

Cool huh? Use your creativity and this DATE function can get you far.

Try getting last day of previous month by using 0 as input for day
=DATE(2012, 13,0) returns 31 December, 2012
=DATE(2012, 12,0) returns 30 November, 2012

Or you just want to get the date after 40000 days later from a date, say 28 Feb 12

=DATE(2012, 2, 28+40000) returns 18 October, 2101

Oh well I will be gone by then and Google might succeed in taking over the world.

Get last day of previous month

=EOMONTH(TODAY(), -1)

Use this function to get last day of previous month from today.


EOMONTH Function is only available for Excel 2007 and above

If EOMONTH function is not available, use this:
=DATE(YEAR(TODAY()), MONTH(TODAY()), 0)

The DATE function will return a date value when you enter in the year, month and day as inputs, what is magical about the DATE function is displayed in my example. I used 0 as the last input for day, this automatically returns the last day of the previous month :)

Get Last day of the month for any date

=EOMONTH(D1,0)

Use this formula to get the last day of the month for the date, D1 is the cell containing the date.

EOMONTH function is only available for Excel 2007 and above, to get last day of the month for older versions of Excel, use this:

=DATE(YEAR(D1),MONTH(D1)+1, 0)

Note:
The DATE function will return a date value when you enter in the year, month and day as inputs, what is magical about the DATE function is displayed in my example. I used 0 as the last input for day, this automatically returns the last day of the previous month,  notice I add one to month? :)