Using IF and IFERROR, but it's only returning an empty string

dred

My formula first checks if Decision or PM Status is any of the following values. If true, it returns an empty string. If false, it checks if the Sprint Delivery date is earlier than today. If true, it returns the Request ID. If false or an invalid date, it returns an empty string. Here is the formula:

=IF(OR(Decision@row = "On Hold", Decision@row = "Complete", Decision@row = "Cancelled", Decision@row = "Rejected", [PM Status]@row = "On Hold", [PM Status]@row = "Complete", [PM Status]@row = "Cancelled", [PM Status]@row = "Rejected"), "", IFERROR(IF([Sprint Delivery]@row < TODAY(), [Request ID]@row, ""), ""))

My goal is to create a list of request id's for tasks that are currently in progress (hence, not on hold, complete, cancelled, or rejected) where the sprint delivery date is past due. The issue that I am running into is that it's only returning empty strings. I believe the issue may have something to do with the Sprint Delivery column. This column is of date type but contains a formula that references a column in another sheet, and that column contains a formula to reference another column in the same sheet. I tried creating a helper column in my sheet to convert the dates to fixed dates, but it's still returning empty strings. I also tried debugging by removing the IFERROR statement, but then received Invalid Data Type error. This is what led me to believe the error has something to do with the Sprint Delivery.

Matt Lynn-PCG

@dred Try this:

=IF(OR(Decision@row = "On Hold", Decision@row = "Complete", Decision@row = "Cancelled", Decision@row = "Rejected", [PM Status]@row = "On Hold", [PM Status]@row = "Complete", [PM Status]@row = "Cancelled", [PM Status]@row = "Rejected"), "", IF([Sprint Delivery]@row < TODAY(), [Request ID]@row, ""))

Shanky Paul

Create a helper column in your current sheet to test whether the date in "Sprint Delivery" is valid and recognized as a date. You can use the following formula:

=IF(ISDATE([Sprint Delivery]@row), "Valid Date", "Invalid Date")

If this works, try the below formula:

=IF(OR(Decision@row = "On Hold", Decision@row = "Complete", Decision@row = "Cancelled", Decision@row = "Rejected", [PM Status]@row = "On Hold", [PM Status]@row = "Complete", [PM Status]@row = "Cancelled", [PM Status]@row = "Rejected"), "", IF(ISDATE([Sprint Delivery]@row), IF([Sprint Delivery]@row < TODAY(), [Request ID]@row, ""), ""))

dred

dred

@Matt Lynn-PCG Hi, thank you for your help. This also returns an empty string

Matt Lynn-PCG

Something else is wrong. I recreated this and it works for me. I’ll look again.

dred

@Shanky Paul Hi, thank you for your help. It returns Invalid Date. How can I fix this? I have posted a few screenshots below of the column formula, and the column it references.

My sheet:

The sheet that is referenced:

Sprint external is of column type and formatted like this:

dred

@Matt Lynn-PCG I agree. I can't seem to figure out the root cause. I added a few screenshots above of the column formula and the sheet it references. I tested to see if it was a valid date or not and it returned invalid. I am unsure how to correct this issue.

Matt Lynn-PCG

@dred That's the problem. The formula we provided above was under the assumption that the Sprint was a date type. This is not a date, it's a text string that has no date logic.

You'll need to change that into a date format. I've tested this on my end and it works for both single digit and double digit months. Use this (or something similar) to replace your formula in the "Sprint Delivery" column:

=DATE(VALUE(20 + RIGHT([Sprint (External)]@row, 2)), VALUE(SUBSTITUTE(MID([Sprint (External)]@row, FIND("|", [Sprint (External)]@row) + 2, 2), "/", "")), 1)

NOTE: Since your string doesn't include a day, it only includes month and year I'm defaulting the day to 1 of each month.

dred

@Matt Lynn-PCG Hi, so the string represents the month and day. In the screenshot above, Q3-2023 - S6 | 9/22, represents 9/22/23. The provided formula changes the dates completely and returns invalid value for some cells. I checked the column properties for Sprint Delivery Date and it does show that it is a date type. Is there another way to ensure it gets recognized as such?

Matt Lynn-PCG

@dred … Ahhh I see your point. End of the day it'll still have to be pulled out via formula back into a date. I can revise the formula and provide that later.

Q3-2023 - S6 | 9/22 is not a date but a text string and it'll never read /logic flow as a date. My method of pulling it out as date() is the only way and it will work, but the previously provided syntax needs updated.

Try this:

=DATE(VALUE(MID([Sprint (External)]@row, 6, 2)), VALUE(SUBSTITUTE(MID([Sprint (External)]@row, FIND("|", [Sprint (External)]@row) + 2, 2), "/", "")), VALUE(RIGHT([Sprint (External)]@row, 2)))

dred

Yes, it's recognizing the dates correctly but still returning invalid value for some. The pattern I noticed is all of the dates that only have a single digit for the day are not recognized (ex. 2/23 vs. 4/7)