ISBLANK formula within IF formula

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.

Find more posts tagged with

Formulas

Comments

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

If this and this are true

Under Construction:

If this and this are true

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.

Formula Screen.JPG

Paul Newcome

Date 72 and Date 82.

Are 72 and 82 row references?

M. David

Yes. Those are rows in the original sheet.

Sign in to join the conversation!

Quick Links

Trending discussions

SUMIF function not working
=SUMIFS({GM Compliance Trained}, {GM Compliance Training Dept}, Department@row , {Compliance Training Date}, MONTH(7, 8, 9)) GM Compliance Trained is the number of employees trained. I am trying to sum the number of employees trained in each department during 3rd quarter (July 1-Sep 30). Thinking I need an IFERROR in there…
Prevent due dates from falling within the same calendar week?
Hi. I am working on a (roughly) annual document review system, in partnership with another team in my organization. They have requested that they receive one document to review per week. But there are more documents to review than there are weeks in the year. So I need to figure out a method for ensuring that I don't send…
How to record date without time
I would like to use automation on a sheet to record the Open and Closed date for a given RAID item. I don't want to record the time, however. Is there a way to exclude a time stamp from the automation? If it is not possible to exclude the time stamp, what formula would I need to use to extract the datae and time into…