#### arogers

##### New Member
I am trying to use cascading if's to make my spreadsheet more automated. The idea is to automatically update a days pending cell as a project moves through different stages.

Here is my code:

=IF(M17="",DAYS360(L17,NOW()),IF(L17="",DAYS360(J17,NOW()),IF(J17="",DAYS360(H17,NOW()),IF(H17="",DAYS360(F17,NOW()),IF(F17="",DAYS360(E17,NOW()),IF(E17="",DAYS360(D17,NOW()),IF(D17="",D17,DAYS360(D17,NOW()))))))))

D column represents oldest date value and M represents most recent date value.

### Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Does the following

=DAYS360(LOOKUP(9.99999999999999E+307,CHOOSE({1,2,3,4,5,6},D17,E17,F17,H17,J17,L17)),TODAY())

deliver the good you are after?

Try this regular formula:
Code:
``=DAYS360(MAX(IF(ISNUMBER(MATCH(COLUMN(D17:M17),{4,5,6,8,10,12,13},0)),D17:M17)),TODAY())``
Is that something you can work with?

Try this regular formula:
Code:
``=DAYS360(MAX(IF(ISNUMBER(MATCH(COLUMN(D17:M17),{4,5,6,8,10,12,13},0)),D17:M17)),TODAY())``
Is that something you can work with?

Yes! This worked! Can you explain the code so I can apply the same principle to other parts of my worksheet?

LOOKUP. formula should also work. If M7 is also a possible target for DAYS360, you can add that to the CHOOSE bit.

For this example, I entered first-of-month dates for Jan through Apr in D17, E17, F17, AND H17
In this formula (for today, 29-Aug-2014):
=DAYS360(MAX(IF(ISNUMBER(MATCH(COLUMN(D17:M17),{4,5,6,8,10,12,13},0)),D17:M17)),TODAY())
The IF function section checks if the referenced column numbers match the list of column numbers I'm interested in.
If yes, it returns the date from the corresponding cells (or false if the cell is blank)
=DAYS360(MAX(IF(ISNUMBER(MATCH({4,5,6,7,8,9,10,11,12,13},{4,5,6,8,10,12,13},0)),D17:M17)),TODAY())
=DAYS360(MAX(IF(ISNUMBER({1,2,3,#N/A,4,#N/A,5,#N/A,6,7}),D17:M17)),TODAY())
=DAYS360(MAX({41640,41671,41699,FALSE,41730,FALSE,0,FALSE,0,0}),TODAY())
=DAYS360(41730,41880)
=148

I hope that helps.

Even if that formula is amended to include M17:
Code:
``=DAYS360(LOOKUP(9.99999999999999E+307,CHOOSE({1,2,3,4,5,6,7},D17,E17,F17,H17,J17,L17,M17)),TODAY())``
it still returns date serial numbers if any of the last referenced cells are blank.

The CHOOSE function returns zeros for blank cells
If L17 and M17 are blank, the LOOKUP function returns zero...causing the DAYS360 function to return the date serial number for TODAY()
instead of the day difference between the latest date and today.

However, if you use this approach...it works:
Code:
``=DAYS360(MAX(CHOOSE({1,2,3,4,5,6,7},D17,E17,F17,H17,J17,L17,M17)),TODAY())``

...
However, if you use this approach...it works:
Code:
``=DAYS360(MAX(CHOOSE({1,2,3,4,5,6,7},D17,E17,F17,H17,J17,L17,M17)),TODAY())``

That's ok with me

Thanks for you help! It's starting to make sense, but where do you get the numbers for the array?

Thanks for you help! It's starting to make sense, but where do you get the numbers for the array?

CHOOSE assembles the values of the various cell into an array constant, something like:

[1;5;0.9;56;....}

Replies
5
Views
292
Replies
1
Views
178
Replies
6
Views
866
Replies
3
Views
257
Replies
2
Views
375

1,220,987
Messages
6,157,239
Members
451,407
Latest member
vdaesety

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