Formula with IF, TODAY and MAX not working.

[reberryjr]

I'm not sure why this formula isn't working. If AZ2 = "", then I want to return the value of 0. If it isn't null, I want to get the number of days between Today and the max date in column AZ.

=IF(AZ2="",0,TODAY()-(MAX(Financials!AZ:AZ)))

When I enter the formula on the Financials sheet, it works fine (obviously without the sheet reference).

Thoughts?

 

[reberryjr]

Yes

 

[reberryjr]

I just figured it out. I'm an idiot. AZ2 is also on the Financials tab, but I wasn't referring to it in the first part of the formula. SMDH!

 

[Aladin Akyurek]

This AZ2 is a cell of Financials!AZ:AZ?

And in which sheet are you running your formula?

I just figured it out. [...] AZ2 is also on the Financials tab, but I wasn't referring to it in the first part of the formula. SMDH!

So AZ2 is a cell of Financials!AZ:AZ... But then the formula you have, adjusted for this fact:

=IF(Financials!AZ2="",0,TODAY()-(MAX(Financials!AZ:AZ)))

is hard to understand. Why not just the following?

=TODAY()-MAX(Financials!AZ:AZ)

 

[reberryjr]

Because if AZ2 = 0, I don't want it to calculate anything.

 

[Aladin Akyurek]

Because if AZ2 = 0, I don't want it to calculate anything.

I don't think this is clear at all.

Suppose we have i Sheet1 the following:

A2 = empty

A3 = 2018-04-17

A4 = 2016-04-15

Given this set of data, we would have:

=TODAY()-MAX(Sheet1!A:A)

which gives 2.

So it's not understandable (for me) to have as you do:

=IF(Sheet1!A2="",0,TOADAY()-MAX(Sheet1!A:A))

Maybe I'm missing something obvious.

 

[reberryjr]

If AZ2 is null, there wouldn't be a date underneath it. Thanks!!

 

[Aladin Akyurek]

If AZ2 is null, there wouldn't be a date underneath it. Thanks!!

Why don't you explain your set up using the example I provided? But no problem, if you choose not to elaborate any further into this.

 

[reberryjr]

If I run a formula where I don't evaluate AZ2, and there isn't a date in AZ2, the formula returns a value of 43209, which I don't want. Essentially, if a Client selected a service, their start date would be listed in AZ2. I do appreciate the assistance!

 

[reberryjr]

@Aladin Akyurek, although your questions did prompt to think deeper into the process, and I need to reevaluate the approach. If the word "Active" is found in column AY, then I would want to run Today()-MAX(AZ:AZ)

I tried this formula, thinking that the 0 in the false portion of the IF statement would return 0 if the word Active wasn't found. However, it's just returning N/A.

=IF(MATCH("Active",Financials!AY:AY,0),TODAY()-MAX(Financials!AZ:AZ),0)

 

[Aladin Akyurek]

That would be:

=IF(ISNUMBER(MATCH("Active",Financials!AY:AY,0)),TODAY()-MAX(Financials!AZ:AZ),0)

Again, I miss the correlation between "Active" and the MAX date.

The following would be an example of what I mean by a correlation:

=IFERROR(TODAY()-VLOOKUP("Active",Financials!AY:AZ,2,0),0)