Alternative to Eomonth formulae question

[xluser2007]

Hi All,

I am using Excel 2003, and found that Conditional Formatting does not accept Analysis toolpak formulae such as EOMONTH.

As such these need to be reverse engineered using standard Excel formulae.

I came across Andrew Engwirda's fantastic solution here:

Basically to put it in this post, i will re-write it here:

[vba]=DATE(YEAR(A1),MONTH(A1)+B1+1,0)[/vba] B1 = Months to add (and A1 = date that you are applying the formula to).

I am confused why this works?

e.g. it uses DATE(YEAR(A1),...), does the YEAR(A1) part not fix the year to be the year for the current date, even if you add add 1000 months to it?

Any help appreciated,

[Bob Phillips]

It does, but the starting point for the year, but Excel is smart (you knew that surely <g>?) and it knows that if you add 13 months that it is a year and 1 month.

The clever part IMO is that you can have a 0 value or even negative values, and Excel works backwards, so the 0th day is the day before the 1st, i.e. the last day. BUt here you have to be careful, as an example, what do you think =DATE(2008,-1,0) returns? The answer is 30th Oct 2007. Logical when you think about it, but not necessarily intuitive.

[xluser2007]

It does, but the starting point for the year, but Excel is smart (you knew that surely <g>?) and it knows that if you add 13 months that it is a year and 1 month.

The clever part IMO is that you can have a 0 value or even negative values, and Excel works backwards, so the 0th day is the day before the 1st, i.e. the last day. BUt here you have to be careful, as an example, what do you think =DATE(2008,-1,0) returns? The answer is 30th Oct 2007. Logical when you think about it, but not necessarily intuitive.

Very Interesting indeed, Bob.

I've just been playing around with what you said. If I try the following formula in a cell: "=DATE(2008,0,10)", it gives the answer as 10/12/2007. When you format it as time, it takes the time as 12:00:00 AM, so it technically belongs to 2007 (the last possible time for the previous year), not technically the start of the year specified i.e. 2008?

So for negative or 0 values for the month in a DATE formula, I guess only this has to borne in mind (which you have already written).

If there is anything ore interesting on this, or if you have any interesting engineered versions of Analysis toolpak functions please let me know.

Also as to my original query, as to why the year changes to suit the changing months in Andrew's formula, I guess you're right in saying that Excel is smart, or maybe its more that smart MVPs such as yourself and Andrew use its features to the fullest !

P.S.

Why does MS not include Analysis toolpak functions as part of standard Excel?

[Bob Phillips]

Very Interesting indeed, Bob.

I've just been playing around with what you said. If I try the following formula in a cell: "=DATE(2008,0,10)", it gives the answer as 10/12/2007. When you format it as time, it takes the time as 12:00:00 AM, so it technically belongs to 2007 (the last possible time for the previous year), not technically the start of the year specified i.e. 2008?

Yes, because as I said the value 0 gives the previous value, so the previous month to 0 is 12.

So for negative or 0 values for the month in a DATE formula, I guess only this has to borne in mind (which you have already

If there is anything ore interesting on this, or if you have any interesting engineered versions of Analysis toolpak functions please let me know.

Yes, there is this nifty little function which adds a number of months to a date. The good thing about it is that it takes care of different days in a month, so that adding 1 month to 30th SEp gives 30th Oct, whilst adding 1 month to 31st Aug gives 30th Sep

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

Also as to my original query, as to why the year changes to suit the changing months in Andrew's formula, I guess you're right in saying that Excel is smart, or maybe its more that smart MVPs such as yourself and Andrew use its features to the fullest !

I think it is serendipity. I don't the desuigners/developers of Excel thought about the functionality to that extent, they just left it a tad loose, and that has been exploited delightfully by talented Excel developers.

I think SUMPRODUCT is my favourite example of this, the definition of SUMPRODUCT by MS is very simple and basic, but look what we have done with it, even MS don't know about it (I was talking to one of their Help authors in April, and he had no idea that you could use SUMPRODUCT as we use it).

It is this looseness, or whatever you want to call it, that is just one reason why Excel is such a great product, it can almost do anything (and many people abuse it because of that).

Why does MS not include Analysis toolpak functions as part of standard Excel?

MS never deemed it important enough until Excel 2007 I guess.