ISBLANK formula within IF formula

M. David

I am wondering how to incorporate an ISBLANK or any other solution into the below IF formula to overcome the issue I am having. The issue is that if Date 82 is blank, then it is automatically giving "Operational" instead of continuing the evaluation of the IF statement. There are no dates entered in either Date 82 or Date 72, so I want it to return a value of "In Development." 

=IF(TODAY() > Date82, "Operational", IF(TODAY() > (Date82 - 60), "Pre-Operational", IF(TODAY() > Date72, "Under Construction", "In Development")))

All help is appreciated.

Thanks.

L_123

=IF(and(not(isblank(date82)),TODAY() > Date82), "Operational", IF(TODAY() > (Date82 - 60), "Pre-Operational", IF(and(not(isblank(date72)),TODAY() > Date72), "Under Construction", "In Development"

M. David

Thanks for the reply. Do I need to add the AND(NOT(ISBLANK to the (Date82-60) as well? I noticed you didn't add it there and I wasn't sure if it was overlooked or it is not needed. I would think it is needed because the ISBLANK doesn't apply to the second evaluation unless it is there. That being said, I am still learning Boolean Code and am not quite sure how this would be evaluated.

Thanks.

Paul Newcome

=IF(NOT(ISBLANK(Date82)), IF(TODAY() > Date82, "Operational", IF(TODAY() > (Date82 - 60), "Pre-Operational", IF(TODAY() > Date72, "Under Construction"))), "In Development")

.

Give this version a try. Basically it says that if Date82 is not blank, run the 3 nested IF's, otherwise "In Development".

L_123

No, you have already checked 82 with the prior if statement. you should avoid redundancy in these formulas as it is extra work for the program and slows it down.

M. David

I appreciate the help, but don't both of those solutions still just check Date 82? What if Date72 is blank? I don't want to have the same issue there where it automatically reports "Under Construction" because Date72 is blank. Also, does Date 82 need to be evaluated to be blank twice? Once for the first evaluation and again for the second where I do Date82-60? 

Paul Newcome

My apologies. I hadn't noticed there were two different rows being referenced.

 

In my solution, you would not need to add the NOT(ISBLANK()) to the second Date82.

 

If Date82 is blank, it will skip over everything else and go straight to "In Development".

.

Is that the outcome you wanted? We could work things so that if either of those dates are blank, or both, or whatever. We just need your exact criteria.

.

What would really help...

 

Spell out you criteria piece by piece for each output. For example...

 

Pre-Operational:

If this is true

or

If this and this are true

 

Under Construction:

If this and this are true

or

If this is true

or

If this is true

.

So on and so forth. If you can lay out your criteria for each output, the building of the formula itself becomes a lot easier.

M. David

Ok, I think I understand. 

I want it first to look at Date82. If today's date is after Date82, then put in Operational. If Date82 is blank, then go to the next evaluation.

I then want it to look at Date 82-60. If today's date is after Date82-60, then put Pre-Operational. If Date 82-60 is blank, then go to the next evaluation.

I then want it to look at Date72. If today's date is after Date72, then put Under Construction. If Date72 is blank, then put In Development.

This would mean that the default is In Development until we hit Date72. It would say Under Construction until we hit Date82-60 at which point it would change to Pre-Operational. When we hit Date82, it would change to Operational. 

I think I explained this all correctly. Your help is appreciated.

 

Paul Newcome

Ok. But wouldn't Date82 - 60 also be blank if Date82 is blank?

.

 

M. David

Yes, Date82-60 would be blank if Date82 is blank, but I don't know if once you put in that comma and go to the next IF statement, it ignores previous steps. If it does, then it wouldn't recognize that Date82 is blank automatically, as it would need to re-evaluate it to see if it's blank. If it does not reset with each IF statement, then, yes, we would only need to evaluate Date82 one time to see if it's blank. 

Paul Newcome

=IF(ISDATE(Date82), IF(TODAY() > Date82, "Operational", IF(TODAY() > Date82 - 60, "Pre-Operational")), IF(AND(ISDATE(Date72), TODAY() > Date72), "Under Construction", "In Development"))

.

The above formula is based off of the following portion of your previous comment:

 

"I want it first to look at Date82. If today's date is after Date82, then put in Operational. If Date82 is blank, then go to the next evaluation.

I then want it to look at Date 82-60. If today's date is after Date82-60, then put Pre-Operational. If Date 82-60 is blank, then go to the next evaluation.

I then want it to look at Date72. If today's date is after Date72, then put Under Construction. If Date72 is blank, then put In Development."

M. David

Thank you. I put the formula in and verified that it worked correctly. I will have to research the ISDATE formula vs. the ISBLANK formula. I have not used that one before. Thanks again. I know this wasn't an easy request. I will be sure to be very clear with requests in the future. Thanks.

Paul Newcome

Happy to help!

 

The ISBLANK function checks if the cell is blank. The ISDATE function works the same way except it checks to see if the data within a cell is an actual date.

M. David

I appreciate the help, and am sorry to continue bothering you about this one, but it looks like the formula is having some issues. I've created a report of each of our projects along with the dates for "Date72" and "Date82", and then exported to Excel. The first six projects are correct, and should be ignored. Everything from Project 48 on looks correct. For some reason, Projects 35-47 are incorrect. I've studied it and tried to figure out where the issue is to no avail. Can you take a look at this again? Thanks.

Paul Newcome

Date 72 and Date 82.

 

Are 72 and 82 row references?

M. David

Yes. Those are rows in the original sheet.