Keep receiving "Could not convert string to date." Error when trying to automate Date Records

[hernandez24]

I have a form where users will enter product details, among those details is the "Field Date", I am trying to set up automation so that when they submit a form and enter a new record, it takes the "Field Date" and subtracts 11 days from it and then enters the new date into the "Feedback Due" column for that product.

I have a TRIGGER set to recognize when the "Field Date" is not empty. My ACTION is set to Update Record, then in the "Feedback Due" I am trying to use DATEADD({Field Date}, -11, 'days') to calculate the new date.

I just get "Field "Feedback Due" cannot accept the provided value: Could not convert string to date."

Both columns are set to the same date format. I am new to airtable and would appreciate any help anyone can provide to get me going in the right direction.

[TheTimeSavingCo · Accepted solution]

Hm if I were you I'd set up my data like so:

With the formula field being set up like this:


Which would allow me to set up my automation like this:

Link to base

---
I'm curious why you want the "Feedback Due" field to be a date field though.  Is it that the "Field Date" value can be updated after the form is submitted and you want the "Feedback Due" field to remain unchanged?

[hernandez24]

Thank you! that works for me, and yes it is because there will be occasions where we will need to change a due date. (weekends and holidays for example).

I was hoping to be able to perform calculations within the automation, but this way till work too.

[Lucas_Landtwist]

Hi all, I actually have the same issue, but haven't been able to solve it following all the solutions I found. I hope someone can put me in the right direction!

I have a main Table called Acquisitions for a real estate company, with a series of records and information about each deal. I created a Tasks table and an automation that creates a series of tasks when a deal is moved to Won. 

In the Tasks table, I created a "Due Date(+7)" field with a formula:

DATEADD({Start Date}, "+7", "days")

which works fine.

The Start Date field is set to be current day by default and works fine. When the task is automatically created, that field is populated correctly with current date.

I have created a few of those: Due Date(+1), Due Date(+7), etc. Which sets the Due Date 1 or 7 days after the Start Date, for instance.

Now, the issue.

I need each task to have a Due Date and am looking to do so by pointing the Due Date field to a specific Due Date (+1, +7, etc). When setting the automation for the new tasks, I point the "Due Date" Field to one of those, but it returns a Token, or string, not the actual date the shows in the field, as shown in picture:

What am I doing wrong? 

Thanks in advance, Lucas

[TheTimeSavingCo]

Hmm, that's super interesting and I've not seen that before I'm afraid.  If I were you I'd try recreating the formula fields and the automation itself to see if it goes away

As a data point, I just tried setting it up and it seemed to work fine:

Link to base

[Lucas_Landtwist]

Hey Adam, thank you immensely for your help, really appreciate it.

I managed to solve it looking at your Table setup!
What I was doing wrong was that I was creating those Due Date fields in the Task record, and not in the Main table. I set up 2 date fields in the main table (due date+1, due date+7), where the +1 and +7 fields are a formula like (DATEADD(Today(), "+7","days"). I then created a Due date field in the Task table and set the automation to populate that field with either the Due Date+1 or Due Date+7 fields. It works perfectly!

The issue that I'm facing now (of course) is that those dates will change every day, since they depend on the Today() formula, which is not the idea. I'd like the due date to be fixed to +7 days from the day the automation is triggered.
I tried creating a Start Date field in the main table, change the +1 and +7 fields to (DATEADD({Start Date}, "+7","days") and set the automation to print current time on Due Date when triggered, but it's not working.

Any idea?

Thank you again for your support, much appreciated!

[TheTimeSavingCo]

Hmm, so you're populating the 'Start Date' field manually before you trigger the automation, right?  Or is it that you want the automation to populate that as well?

[Lucas_Landtwist]

Hi Adam, 
I just realized that my answer wasn't published and was lost! Thanks again for your time and help!

I would like the automation to populate the Start Date, since it is the time the action is triggered and from that time the Due Date is calculated.

Now, a good surprise I just got is that, even if the original Due Date field (in the Acquisitions table) keeps updating (due to the Today() formula), the Due Date in the Task table (which is set to be equal to the original Due Date by the automation), hasn't updated, so it's actually working!

It would still be interesting to know how to solve the issue of printing the Start Date though, since it will come handy in the future. If you could give me a hand with that, I would thank you a lot.

Thanks again Adam!