Formatting Formula to return just Date

[Jessica_Cote]

Hi,

I am using this formula (DATEADD({Estimated Start Date},Calculation,‘days’) to create the attached result. However, I would like to then use this result to populate “target delivery date” in an automation,
and got an error that it did not like my string. I am assuming it has to with the time. Is there a better formula?

For Context, I need to have Target Delivery Date as stand alone field, not a formula, for various reasons.Hence why I want have an automation to populate it if “Calculated Date” is “Not Empty”

[Ben_Young1]

Hey @Jessica_Cote!

I think you’re correct about your suspicion that the reason the automation doesn’t want to move the value over is because of the time value at the end.

For that, you can use the DATETIME_FORMAT function like so:

DATETIME_FORMAT(
    DATEADD(
        {Estimated Start Date},
        {Calculation}, 
        'days'
    ),
    'l'
)

This will produce a date/time value in the format of 6/20/2022.

---

Out of curiosity, I’m curious as to why you’d like to automate moving the value from the formula into the new date/time field. I’d appreciate a bit of insight!

Let me know if my suggestion breaks the fabric of your table, or if it just plain out doesn’t work!

[Jessica_Cote]

It did not work for me, but possibly because I do not understant what the ‘l’ is at the end?

This one did: DATETIME_FORMAT({Calculated Date},‘MM/DD/YYYY’)

However, the automation those when tested comes up as success, but then errors out when it’s running because it still can’t understand the string. Perhaps it needs to be converted to text?

For insight. Having Target Delivery date, as an actual calendar is really nice right? So if someone is added a date for a project it is simple process, not a lot of thought needed.

For more detailed projects that might need a work back, it’s nice to have a timeline of how long each step will take based off the start date, much like Airtable timeline and Dependencies. This needs to be repeatable.

Both of these type of projects all need to end up in 1 field that I then automate to push to Jira. The current field that is porting over is “Target Delivery Date” So I did not want to mess with it. I am also am not a expert at formulas (obv, nor do want to over complicate things. I have an excel sheet that did all for me in the past, but I was hoping to recreate the basics of it in airtable for obvious reasons. Additionally pushing things to jira is a must and always adds a level complexity or extra thought.

Perhaps there is more advanced script feature (Which I personally do not have access to, but probably could get) that would be a better way to do this, or think about it completely differently. I am open to Ideas.

I guess in the end my Jira Automation just needs to know what date to watch.

cheers~

Here is where I got to. So I guess I just need a field like ‘Date Parse’ and a airtable field like ‘Target Delivery’, to all end up in one field that I can port over to jira.

[kuovonne]

What type of automation are you using? Are you using a native Airtable automation with an update record action to copy the formula result to an editable field?

If it is a native Airtable automation, the display of the time shouldn’t matter. Your formula of

DATEADD({Estimated Start Date}, Calculation, 'days')

should have worked. Was that the complete formula or only part of the formula? Could you have changed the formula without retesting the trigger?

The DATEADD function should work better than the DATETIME_FORMAT function because DATEADD returns a date object while DATETIME_FORMAT returns a string.

Maybe share screen captures of the automation configuration?

Or are you using a different Automation service?