Unwieldy Formula - Too Long

[Evagrius Ponticus]

Hi All,

Below is my primitive formula. It works, but it's tough to work with when making changes. I have absolutely no more room in the formula bar All the cells referenced contain dates excpet column P. Can anyone see a way to compact the formula without altering the reuslt?

=IF($E$7="","",IF(AND($P16="y",$Q16<=X$14,$R16>X$14,$R16-X$14>=365),"12m",IF(AND($P16="y",$Q16<X$14,$R16>X$14,$R16-X$14<365),DATEDIF(X$14,$R16,"m")&"m. "&DATEDIF(X$14,$R16,"md")&"d",""))&IF(AND($R16<=X$14,$S16-X$14>=365),"X",IF(AND($R16<X$14,$S16>X$14,$S16-X$14>0,$S16-X$14<365),DATEDIF($S16,Y$14,"m")&"m. "&DATEDIF($S16,Y$14,"md")&"d",""))&IF(AND($S16<=X$14,$T16-X$14>=365),"12m","")&IF(AND($S16<X$14,$T16-X$14<365,$T16-X$14>0),DATEDIF(X$14,$T16,"m")&"m. "&DATEDIF(X$14,$T16,"md")&"d","")&IF(AND($P16="y",$Q16>X$14,Y$14-$Q16<365,Y$14-$Q16>0),DATEDIF($Q16,Y$14,"m")&"m. "&DATEDIF($Q16+1,Y$14,"md")&"d",IF(AND($Q16>=X$14,OR(Y$14-$Q16<=0,Y$14-$Q16>365)),"X",""))&IF(AND($T16<=X$14,$U16-X$14>=365),"X","")&IF(AND($U16<=X$14,$V16-X$14>=365),"12m",IF(AND($U16<=X$14,$V16-X$14<365,$V16-X$14>0),DATEDIF(X$14,$V16,"m")&"m. "& DATEDIF(X$14,$V16,"md")&"d",""))&IF(AND($P16="n",$Q16<X$14,$R16-X$14>=0),"X","")&IF(AND($U16>X$14,$U16-X$14<365),DATEDIF($U16,Y$14,"m")&"m. " &DATEDIF($U16,Y$14,"md")& "d","")&IF(AND(X$14>$V16),"-",""))

 

[RoryA]

Can you explain what dates are in what cells and what the formula does (in plain English) so we don't have to try and decipher it?

 

[Evagrius Ponticus]

Well, all the dates vary Rorya. Basically, all the formula does is find the difference between two dates and spits out in month and days what that difference is. if the difference between the dates is negative, then the formula generates an "X", if the difference is greater than one year, then it generates "12m." If the difference is less than a year, then I get the number of month's and "M. " and the number of days and "d" - i.e. "7m. 17d"

Hope that makes sense

 

[Fazza]

Hi,

Are there many of these in a workbook?

Maybe it would be easier to manage via a UDF (user defined function) in VBA?

Or via some extra cells. Or some defined names to do some of the steps in smaller increments?

 

[Evagrius Ponticus]

ADVERTISEMENT

Hi Fazza, I thought of a UDF but I don't know how to create those yet. The Formula is in one worksheet across 20 columns down an uncertain # of rows.

 

[SteveO59L]

I think that I would be inclined to tabulate the conditions and outcomes and use vlookup()

 

[edvwvw]

It may be worth looking at the formula below - It checks the difference between 2 dates.

(credit to the original poster)

I am sure that it would be straightforward to amend exactly as you require.

Excel Workbook
	A	B	C
2	1/1/2007	10/6/2008	1 years 5 months 9 days
Sheet1

edvwvw