The interesting DATE() formula

[BNCOXUK]

Hi, I just have a question about the DATE() function.

If cell A1 has the date of 30/06/2012, then the formulae:

=DATE(YEAR(A1),MONTH(A1)-1+1,) will return the value 31/05/2012

=DATE(YEAR(A1),MONTH(A1)-3+1,) will return the value 31/03/2012

This way correctly calculates the date at the end of last month, and 3 months ago.

Can you explain how this worked perfectly?

[BigBas]

I'm not entirely sure of your question, but I will state that in the first example, you don't need to use -1 + 1 since the net of that expression is actually 0. The formula =DATE(YEAR(A1),MONTH(A1),) should do exactly the same things.

The reason it works, if needed in the future, is that the formula sets to the year in cell A1 and the month in cell A1. Since the final part of the formula is left blank (after the last comma), excel treats it as if it were a zero. So, in essence, Excel treats the formula as the 0th day of June 2012. Of course, it treats the 0th day of a month as actually the last day of the prior month.

Does this help at all?

[BNCOXUK]

Thanks a lot for your good explanation.