Unwieldy Formula - Too Long

Evagrius Ponticus

Well-known Member
Joined
May 24, 2007
Messages
1,467
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?

Code:
=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),"-",""))
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
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? :)
 
Upvote 0
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 :)
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
I think that I would be inclined to tabulate the conditions and outcomes and use vlookup()
 
Upvote 0
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
ABC
21/1/200710/6/20081 years 5 months 9 days
Sheet1


edvwvw
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,947
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top