Save the date! Join us on October 16 for our Product Ops launch event. Register here.
May 24, 2023 12:10 PM
I'm working with some data coming from another group and they use both text and dates in a fields that I need to pull apart and use the dates to estimate further due dates down the line. I'm trying to avoid the big #ERROR! when the field has text and not a date. Is there a way to pull out just dates into another field (or in the formula I'm already using) so it ignores the text?
May 24, 2023 05:05 PM
In your "AND", try adding another criteria of `{EST} != "NO RIGHTS"` so that your "DATETIME_FORMAT" will only run on dates?
May 24, 2023 05:37 PM
I considered that but unfortunately there's no consistency to what text will be used in that space and since this data comes from another team, I'm trying to find a work around without them having to change their process. Otherwise I think that would be a great solution!
May 24, 2023 05:42 PM
Ah I see! In that case, what if we used an "IF" to check if the "DATETIME_FORMAT" function results in an error like so:
IF(
NOT(
ISERROR(
DATETIME_FORMAT(Date,"YYYY")
)
),
DATETIME_FORMAT(Date,"YYYY")
)