Identify blank cell output from formula in Date column

dylanchaney

I'm having a problem trying to capture a blank value from my Date column. I have 4 date columns:

1. Planned Start Date
2. Planned End Date
3. Actual Start Date
4. Actual End Date

My goal is to capture an error message in a [DateError] helper column based on the lack of input in these columns. For example, IF(AND([Planned End Date]@row < TODAY(), [Actual End Date]@row = "", [Actual Start Date]@row <> ""), "Project has ended & no recorded date", "") provides me the result "Project has ended..." I have a nested IF formula that captures everything I want, except for when I get to the end where I want to display an error message when [Planned Start Date] is blank. My problem is that the cell actually contains a formula, and outputs either a date value or a blank value. Why can't I reference the blank value output in my secondary formula?

DateError formula: =IF(AND([Planned End Date]@row < TODAY(), [Actual End Date]@row = "", [Actual Start Date]@row = ""), "Project has ended & no actual dates", IF(AND([Planned End Date]@row < TODAY(), [Actual End Date]@row = "", [Actual Start Date]@row <> ""), "Project has ended & no date recorded", IF(AND([Planned Start Date]@row < TODAY(), [Actual Start Date]@row = ""), "Project has started & no recorded date", IF([Planned Start Date]@row > TODAY(), "", IF(AND([Planned Start Date]@row < TODAY(), [Planned End Date]@row > TODAY()), "", IF([Planned End Date]@row < TODAY(), "", IF(ISBLANK([Planned Start Date]@row), "No project dates", "")))))))

Planned Start Date formula: =IFERROR(IF(INDEX({External Sheet Reference}, [Index Order]@row) = "", "", DATE(1900, 1, 1) - 2 + INDEX({External Sheet Reference}, [Index Order]@row)), "")

Any help would be greatly appreciated!

Scott Orsey

Have you tried [Planned Start Date]@row="" instead of the ISBLANK() function?

dylanchaney

@Scott Orsey, yes I've tried identifying that cell with = "", = false, and = 0 after the [Planned Start Date]@row, all of which work in a separate helper cell (to test values before I call them in my formula). I've even used NOT and ISDATE functions to no avail.

I have to assume that the issue lies with the fact that I have a formula in a date column and I'm trying to call that output in my new formula. From what I understand, the formulaic logic is sound, but the intercell JSON might not be able to handle this request.

Scott Orsey

Hmm... That's a tough one. You're formula is long so tough to determine. To confirm, if you remove the final IF() statement then the formula doesn't produce an error? Since you have the helper cell, have you considered building your formula starting with the working value? Then, instead of using this condition as your last check, maybe use it as your first check. Then, if Start Date is missing, you don't even need to evaluate the rest. For example:

=IF(ISBLANK([Planned Start Date]@row), "No project dates", IF(AND([Planned End Date]@row < TODAY(), [Actual End Date]@row = "", [Actual Start Date]@row = ""), "Project has ended & no actual dates", IF(AND([Planned End Date]@row < TODAY(), [Actual End Date]@row = "", [Actual Start Date]@row <> ""), "Project has ended & no date recorded", IF(AND([Planned Start Date]@row < TODAY(), [Actual Start Date]@row = ""), "Project has started & no recorded date", IF([Planned Start Date]@row > TODAY(), "", IF(AND([Planned Start Date]@row < TODAY(), [Planned End Date]@row > TODAY()), "", IF([Planned End Date]@row < TODAY(), "", "")))))))

If all else fails, perhaps you could use IFERROR to capture that situation.

Hope this helps.

dylanchaney

@Scott Orsey that helped thank you! Yes, that's correct that if I remove the, let's call it "blank validation", from the formula, it ends up working fine for all other error validations, except the lack of a start date.

What I just figured out just now is if I move that validation to the front of the formula like you suggested, it works for the purpose I want it to and then I just had to add one more IF to be able to validate against empty rows and I had a successful column formula! In this case I validated against my project numbers and names, the two absolute values in the data entries.

Final formula, with changes in bold italics: =IF(AND([Project Number]@row <> "", [Planned Start Date]@row = ""), "No project dates", IF(AND([Project Name]@row = "", [Project Number]@row = ""), "", IF(AND([Planned End Date]@row < TODAY(), [Actual End Date]@row = "", [Actual Start Date]@row = ""), "Project has ended & no actual dates", IF(AND([Planned End Date]@row < TODAY(), [Actual End Date]@row = "", [Actual Start Date]@row <> ""), "Project has ended & no date recorded", IF(AND([Planned Start Date]@row < TODAY(), [Actual Start Date]@row = ""), "Project has started & no recorded date", IF([Planned Start Date]@row > TODAY(), "", IF(AND([Planned Start Date]@row < TODAY(), [Planned End Date]@row > TODAY()), "", IF([Planned End Date]@row < TODAY(), ""))))))))

Scott Orsey

Glad you got it to work. That's great!

Be well

Andrée Starå

Hi @dylanchaney

I hope you're well and safe!

Excellent! I'm glad you got it working!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Please support the Community by marking your post with the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!