DATE function clarification

[Guest]

Hi,

I have been using this formula to subtract 2 months from D15, it also gives
you the last day of the month, i.e. if D15 was May 31, the formula gives you
Feb 28 instead of Feb 31.

=MIN(DATE(YEAR(D15),MONTH(D15)+{-1,-3},DAY(D15)*{0,1}))

My question is that I don't understand the Min and {} brackets??? How
exactly does this work...I'd like to understand it better!

~Jess

 

[Bob Phillips]

Your formula is actually wrong it should be

=MIN(DATE(YEAR(D15),MONTH(D15)+{-2,-3},DAY(D15)*{0,1}))

and it subtracts 3 months not 2.

What is doing is effectively to do two calculations, one for the same day
and 3 months off, and one for the 0th day of 2 months off. The thing is that
if you take 3 months off of say May 31st, it gives you the 31st day of Feb,
which Excel 'smartly' translates to 3rd March. The other calculation, the
0th day of 2 months off gives the 0th day of March, which again, Excel
'smartly' translates to the last day of the previous month, 28th Feb in the
example. The MIN then takes the lower of these two dates to ensure that
months with different number of days are catered for.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)