# Unwieldy Formula - Too Long

#### Evagrius Ponticus

##### Well-known Member
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

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
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?

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

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?

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.

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

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

Replies
0
Views
371
Replies
11
Views
240
Replies
5
Views
120
Replies
3
Views
63
Replies
4
Views
665

1,196,128
Messages
6,013,616
Members
441,777
Latest member

### 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.

### Which adblocker are you using?

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

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