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

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")))

Best Answer

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    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))

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Answers

  • 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

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • jwilson
    jwilson ✭✭✭
    edited 09/29/23

    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.
    Genevieve P. Employee
    Answer ✓

    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))

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!