Blank when #ERROR! while Date Formatting

[Dan_Emmerie]

Thank you ahead. This is probably an easy one, but I could use the help since I’m not good at formulas or programming. Basically I am getting data from an API that is giving me IDs that need to be matched with Values. I got that happening but where the Values are Blank, I get an error, possibly because it’s trying to format it into a date. The errors are keeping it not working with some reports, so need them to result in blank() when erroring. Here’s my formula:

DATETIME_PARSE((IF({Custom field items 1 id custom field}=“5a9866ffd6afbd6de1c0d07b”,{Custom field items 1 value date})&IF({Custom field items 2 id custom field}=“5a9866ffd6afbd6de1c0d07b”,{Custom field items 2 value date})&IF({Custom field items 3 id custom field}=“5a9866ffd6afbd6de1c0d07b”,{Custom field items 3 value date})&IF({Custom field items 4 id custom field}=“5a9866ffd6afbd6de1c0d07b”,{Custom field items 4 value date})&IF({Custom field items 5 id custom field}=“5a9866ffd6afbd6de1c0d07b”,{Custom field items 5 value date})&IF({Custom field items 6 id custom field}=“5a9866ffd6afbd6de1c0d07b”,{Custom field items 6 value date})))

[Dan_Emmerie]

I was thinking it could be solved by doing if FIELD equals ID, return VALUE, else return blank. but when i put a ,BLANK() it still shows an error. Perhaps because the value is a date?

[Ben_Young1]

Hey @Dan_Emmerie

Huh… Something catches my immediate attention about your formula.

If any of those six fields can return the same value, hence the reason you need to check all of them, then you might want to reconsider how your table/base is designed since you’re subjecting yourself to some rather harsh scenarios when you need to deal with the data itself.

This formula of having to check every field is a great example.
In the meantime, you might want to consider changing the names to something a bit more user-friendly.

Now… here’s my version of your formula with the syntax corrected.
If none of the conditions return true, then it will simply return blank.
This will resolve the error from being returned.

DATETIME_PARSE(
    IF(
        OR(
            {Custom field items 1 id custom field},
            {Custom field items 2 id custom field},
            {Custom field items 3 id custom field},
            {Custom field items 4 id custom field},
            {Custom field items 5 id custom field},
            {Custom field items 6 id custom field}    
        ),
        IF(
            {Custom field items 1 id custom field} = “5a9866ffd6afbd6de1c0d07b”,
            {Custom field items 1 id custom field},
            IF(
                {Custom field items 2 id custom field} = “5a9866ffd6afbd6de1c0d07b”,
                {Custom field items 2 id custom field},
                IF(
                    {Custom field items 3 id custom field} = “5a9866ffd6afbd6de1c0d07b”,
                    {Custom field items 3 id custom field},
                    IF(
                        {Custom field items 4 id custom field} = “5a9866ffd6afbd6de1c0d07b”,
                        {Custom field items 4 id custom field},
                        IF(
                            {Custom field items 5 id custom field} = “5a9866ffd6afbd6de1c0d07b”,
                            {Custom field items 5 id custom field},
                            IF(
                                {Custom field items 6 id custom field} = “5a9866ffd6afbd6de1c0d07b”,
                                {Custom field items 6 id custom field}
                            )
                        )
                    )
                )
            ) 
        )
    )
)

Now, if there’s something that tosses you an error or if you’re not getting your desired results, please let us know!
We’ll definitely need more context/information though.

[Dan_Emmerie]

Thanks for the help! I copied the formula but it’s not letting me save it saying it’s invalid? I also cannot change the field names because that’s the IDs and Values that the API is returning. I’m trying to get a project management app to one-way sync to airtable so we can migrate to airtable.

Not sure it helps anything, but the formula I was using works fine except for returning an error when there is no value date provided.