Return Text that is in a Data/Number Column that captures dates

jwilson

In the table below, I have 3 columns that are text/number fields: Final Document, Target Date, Actual Date.

In the Target MM/YYYY and the Actual MM/YYYY, I have a formula to pull the MM/YYYY from the Target date and Actual Date, but as you can see, there is "On Hold" and "Cancelled" text instead of a date in some of the cells. I'm trying to pull the date, otherwise, return the text in the cell . If the cell is empty, I want it to return "Active". Here are the formulas:

Target MM/YYYY:

=IFERROR(IF(MONTH([Target Date]@row) < 10, "0", "") + MONTH([Target Date]@row) + "/" + YEAR([Target Date]@row), "Active")

Actual MM/YYYY:

=IFERROR(IF(MONTH([Actual Date]@row) < 10, "0", "") + MONTH([Actual Date]@row) + "/" + YEAR([Actual Date]@row), "Active")

The three fields that are highlighted in gray are my formula columns.

Status:

With the status column, if the cell is empty, it should return "Active", if the cell has text, it should return the text, and if there is a date, it should return Complete.

=IF([Actual Date]@row = "", "Active", IF([Actual Date]@row = "On Hold", "On Hold", IF([Actual Date]@row = "Cancelled", "Cancelled", "Complete")))

Genevieve P.

Hi @jwilson

My apologies, I added in criteria without testing the original, full formula.

If I'm understanding you correctly, you could just use this:

=IF([Target Date]@row = "", "Active", [Target Date]@row + "")

This says, if it's blank return "Active", otherwise bring through the same date (but I added + "" to turn it into text for your Text/Number column).

If you're wanting to keep your specific formatting, I added the IF statement in the wrong place - we'll need to keep in the extra IF to check the month:

=IF([Target Date]@row = "", "Active", IF(ISDATE([Target Date]@row), IF(MONTH([Target Date]@row) < 10, "0", "") + MONTH([Target Date]@row) + "/" + YEAR([Target Date]@row,[Target Date]@row, [Target Date]@row))

and:

=IF([Actual Date]@row = "", "Active", IF(ISDATE([Actual Date]@row), IF(MONTH([Actual Date]@row) < 10, "0", "") + MONTH([Actual Date]@row) + "/" + YEAR([Actual Date]@row), "Active", [Actual Date]@row))

Genevieve P.

Hi @jwilson

For your first two formulas, I would add an IF statement to first check if the cell is blank so it can return "Active". Otherwise, it will check if the cell has a Date using ISDATE, and if it is, continue with your current formula. Otherwise if it's not a date, simply return whatever is showing in that cell:

=IF([Target Date]@row = "", "Active", IF(ISDATE([Target Date]@row), MONTH([Target Date]@row) < 10, "0", "") + MONTH([Target Date]@row) + "/" + YEAR([Target Date]@row,[Target Date]@row, [Target Date]@row))

=IF([Actual Date]@row = "", "Active", IF(ISDATE([Actual Date]@row), MONTH([Actual Date]@row) < 10, "0", "") + MONTH([Actual Date]@row) + "/" + YEAR([Actual Date]@row), "Active", [Actual Date]@row))

Your Status formula looks to be working correctly! Do you need any help here?

Cheers,

Genevieve

jwilson

Thank you for replying, @Genevieve P.

I copied and pasted the formula in each column.

In the Target MM/YYYY column, I am receiving an #INCORRECT ARGUMENT SET.

In the Actual MM/YYYY column, I receive a pop-up stating, "The column formula syntax isn't quite right, see our help article." This might be due to the error in the Target MM/YYYY column.

Genevieve P.

Hi @jwilson

My apologies, I added in criteria without testing the original, full formula.

If I'm understanding you correctly, you could just use this:

=IF([Target Date]@row = "", "Active", [Target Date]@row + "")

This says, if it's blank return "Active", otherwise bring through the same date (but I added + "" to turn it into text for your Text/Number column).

If you're wanting to keep your specific formatting, I added the IF statement in the wrong place - we'll need to keep in the extra IF to check the month:

=IF([Target Date]@row = "", "Active", IF(ISDATE([Target Date]@row), IF(MONTH([Target Date]@row) < 10, "0", "") + MONTH([Target Date]@row) + "/" + YEAR([Target Date]@row,[Target Date]@row, [Target Date]@row))

and:

=IF([Actual Date]@row = "", "Active", IF(ISDATE([Actual Date]@row), IF(MONTH([Actual Date]@row) < 10, "0", "") + MONTH([Actual Date]@row) + "/" + YEAR([Actual Date]@row), "Active", [Actual Date]@row))