Jun 06, 2023 01:14 AM
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?
Jun 06, 2023 01:43 AM
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
Jun 07, 2023 12:48 PM
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:
Jun 08, 2023 01:57 AM
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