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.