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
-
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 -
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.
-
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
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!