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.
Comments
-
=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"
-
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.
-
=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".
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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.
-
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?
-
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.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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.
-
Ok. But wouldn't Date82 - 60 also be blank if Date82 is blank?
.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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.
-
=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."
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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.
-
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.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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.
-
Date 72 and Date 82.
Are 72 and 82 row references?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Yes. Those are rows in the original sheet.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!