Formula to calculate due date formatted so Airtable recognises as a date

[Meagan_Caesar]

Hi, struggling to wrap my head around date formulas, thanks for your patience with all my questions!

I'm trying to set up a formula field that will calculate the next due date for a submission based on when a record was submitted via a form. I have the 'created' field set up, which has a date and time, then the formula field, which I'd like to only show the date, not the time. I need Airtable to recognise this field as a date field for use in Automations, but I also want it to show as a recognisable date to the Author viewing the record and receiving the automated email notification.

This is what I've tried to get to work, but it still comes out as a string when using DATETIME_PARSE, but when only using DATETIME_FORMAT the field won't recognise it as a date. What am I doing wrong?

IF({Type of Edits}='Bio draft edits for Author revision', 

DATETIME_PARSE(

DATETIME_FORMAT(

DATEADD({Submitted},1,'week'),"DD/MM/YYYY"), 

"DD/MM/YYYY"),

" ")

There will be two formula fields, one that returns the date for their final author bio submission, based off when the bio draft was submitted, and the other will use the same formula but for chapter draft and final. I'd like for the formula fields to remain blank for any other types of submissions and only show the due date for those specific things, so it's not confusing, hence the IF statement.

Any help would be greatly appreciated! Thank you

[TheTimeSavingCo]

Hmm, if the formula field's only purpose is to calculate and display the date for the email, you could just remove the "DATETIME_PARSE()" bit and that would be fine

If you need to use that calculated date in another formula field though, then I'm afraid you're going to need to create another formula field just for that purpose.  You would thus end up with:
1. One field with "DATETIME_FORMAT" that you use in your email to display the date nicely
2. One field with without "DATETIME_FORMAT" or "DATETIME_PARSE", and this field will be referenced by the other formula field

The problem we're trying to overcome here is the fact that in email automations, Date fields are displayed in that rather long string that's hard to read, and so we have to use a formula field with "DATETIME_FORMAT" in our automations instead

[Meagan_Caesar]

Thanks for your reply Adam!

I considered that I may need 2 formula fields, but what's confusing me is that in another table I have this formula:

DATETIME_PARSE(

DATETIME_FORMAT(

DATEADD({Launch Date},1,'years'),

"DD/MM/YYYY"),

"DD/MM/YYYY")

No IF statement, and it works perfectly! It shows the date and time, it allows me to go to the formatting tab and toggle off the time and choose how I want the date formatted, etc. When I test the formula I'm trying to fix for this other table without the IF statement, it also works perfectly.

So what I'm stuck on is what I'm doing wrong with the IF statement that's throwing off the rest of the formula, or can I not use an IF statement with that type of formula and will need the 2 separate fields?

Thanks so much!

- Also, on an unrelated note, I've run up against an issue with managing a Base for a Publisher client. When setting up Automations, it won't allow me to select any of the accounts she's connected (e.g. Gmail), so I can't set up emails to be sent from her to her authors, I have to get her to go in and select her accounts and the 'To' field for every Automation. Do you happen to know if there's a workaround for this? I understand why it would be set up this way, but it's also super frustrating and not practical for anyone managing Bases for other people. She's currently on a free account and not planning to upgrade just yet, so using the native 'Send Email' action isn't an option. If you have any tips or info around this I'd really appreciate it!

[TheTimeSavingCo]

re: So what I'm stuck on is what I'm doing wrong with the IF statement that's throwing off the rest of the formula, or can I not use an IF statement with that type of formula and will need the 2 separate fields?

Ah, this is because of the " " at the end.  Try:

IF(
  {Type of Edits}='Bio draft edits for Author revision', 
  DATETIME_PARSE(
    DATETIME_FORMAT(
      DATEADD({Submitted},1,'week'),
      "DD/MM/YYYY"
    ), 
    "DD/MM/YYYY"
    )
)

Once we remove that the formula only outputs a date, so we should be able to access the date formatting options for the field again

---
re: Do you happen to know if there's a workaround for this?

I'm afraid not, so sorry