Help

Upcoming database upgrades. Airtable functionality will be reduced for ~15 minutes at 06:00 UTC on Feb. 4 / 10:00 pm PT on Feb. 3. Learn more here

Automation failing because "Could not convert string to date."

Topic Labels: Automations Formulas
Solved
Jump to Solution
128 3
cancel
Showing results for 
Search instead for 
Did you mean: 
Ashley_Jackson
6 - Interface Innovator
6 - Interface Innovator

I have a task table with start date, due date, and completion date.  Each task is marked open, attempted, or done via a single select.  Working to create an automation that creates a copy of a task marked as "Attempted."  here's the flow of my automation:

Trigger:  Record meets condition / condition is Status field is NOT open (aka, either Done or Attempted)

Action:  Insert the value of a formula field into the completion date field.  The formula field is simply NOW(), so it will change every day and as we complete tasks, the completion date will change to the current date.

Conditional Action:  If Status field equals "Attempted"

Action:  Create new record with the following dynamically inserted fields:

  • Task Description
  • New Start Date
  • New Due Date
  • Assignee

To calculate the new start and end dates, I have created two columns in the Task table with very similar formulas like this:

DATEADD({Completed On}, 2, 'day')

The new due date field adds 4 days and the new start date adds 2 days to the completion date.  The formulas work as expected and the formula field allows me to format the result as a DATE.  The only issue I have is that open tasks result in ERROR for these two formula fields, but as they operate in the background, I'm not overly concerned about that.

When I use these formula fields as the values of my new start and due dates in the newly created record, my runs fail with this error:

Field "Start Date" cannot accept the provided value: Could not convert string to date.

I am confused because the new start and end date fields ARE date formatted values, not strings.

Anyone have any thoughts???

1 Solution

Accepted Solutions

OK, I think I figured a workaround.  The completion date field is a formula that looks up the value a field called Today which uses the simple NOW() formula.  I did it this way bc afaik you can't use calculations INSIDE an automation.  So instead of basing the new start and end dates on the completion date which doesn't get calculated until the automation is triggered... I used the today field which is always available!  Now it works like a gem!  Thanks for your helpful questions - they led to the solution!!!

See Solution in Thread

3 Replies 3

Can you share screen shots of the triggering record and the automation?

Have you retested the trigger for the automation?
When you test the automation, can you expand the screen to see the field values for the triggering record?
Are you getting the formula values from the triggering record, or some other record such as the newly created record?

It is possible that you are somehow getting the formula value with the error instead of a date.

Hi, Thank you for the help!  I did as you asked and here are the screen grabs:

This is the test task I'm using

Screen Shot 2023-01-19 at 1.06.09 PM.png

 

This is the automation overview:

Screen Shot 2023-01-19 at 1.06.23 PM.png

 

This is the result of testing the trigger and I think it's where the problem lies:

Screen Shot 2023-01-19 at 1.07.24 PM.png

It seems like the automation is grabbing the value of the formula fields BEFORE the formulas have a chance to update in the first action.  I thought that by ordering them this way, it would process in that order - but I guess not.  Is it possible to delay the create record step somehow?  Any ideas how to make this work?  TIA!

OK, I think I figured a workaround.  The completion date field is a formula that looks up the value a field called Today which uses the simple NOW() formula.  I did it this way bc afaik you can't use calculations INSIDE an automation.  So instead of basing the new start and end dates on the completion date which doesn't get calculated until the automation is triggered... I used the today field which is always available!  Now it works like a gem!  Thanks for your helpful questions - they led to the solution!!!