That's the "day" part from the formula I posted
here
The formula already gives years and months and that part just calculates the number of days remaining.
Here's a simple example:
B5 = 4th February 2008
C5 = 27th April 2009
The difference is 1 year and 2 months.....then the formula you posted here gives the number of days. Clearly here the answer is 23 so to get that the MIN part of the formula returns the date 4th April 2009 and that's subtracted from the date in C5 to get 23.
All we want to calculate then, with the MIN part, is the day from B5, i.e. the 4th combined with the year and month from C5, but what about where the dates are like this?
B5 = 27th February 2008
C5 = 4th April 2009
When the day of C5 is less than the day of B5 I need to go back a month, i.e. I want to find the day of B5 (the 27th) in the Year and Month of C5 (minus 1 month), i.e. 27th March 2009.
Those 2 possibilities lead to this formula:
=DATE(YEAR(C5),MONTH(C5)-(DAY(C5)< DAY(B5)),DAY(B5))
which will give the correct date for the above two examples......
.....but there are other more complex possibilities, what if my 2 dates were these?
B5 = 31st January 2008
C5 = 13th March 2009
Now the above formula will give 3rd March which I don't want because then the difference will be "1 year, 2 months and 10 days".....but I need to count from 28th February to get 13 days.
That's where the {0,1} comes in.
If you use a simple formula like this
=10+{1,0} that generates an array like this {11,10}, i.e. 10 added to both elements of the "array constant". Similarly this formula
=10*{0,1} generates an array like this {0,10}, i.e. 10 multiplied by each element of the array....
so going back to the DATE formula when B5 and C5 are as follows
B5 = 31st January 2008
C5 = 13th March 2009
then this formula
=DATE(YEAR(C5),MONTH(C5)-(DAY(C5)< DAY(B5)),DAY(B5))
turns into this
=DATE(2009,3-1,31)
which, as stated before, is equivalent to 3rd March 2009.....but when I use my "array constants" in conjunction with MIN I ensure that the date doesn't "spill over" into the next month, i.e.
=MIN(DATE(2009,2+{1,0},31*{0,1}))
is equivalent to
=MIN(DATE(2009,{3,2},{0,31}))
This formula will give the MIN of the two dates. The two dates are DATE(2009,3,0), i.e. 28th February 2009 and DATE(2009,2,31), i.e. 3rd March 2009.
HTH, barry