Due Date Automation

[Jenny_Hislop]

Hi, I’m looking to set up automations to remind me of due dates. I was using a datetimediff formula, but it says NaN. I want to take todays date-the due date and have it show how many days until the due date, then I was going to schedule an automation to send out whenever tasks are due in 3 days or less.
The formula I’m using is: DATETIME_DIFF({Today’s Date}, {Due Date }, ‘days’)

[Kamille_Parks]

The field “Today’s Date” isn’t shown in your screenshot so its unclear whether the problem is actually with that field. Assuming the extra space at the end of {Due Date } isn’t a typo, consider the following formula:

DATETIME_DIFF(TODAY(), {Due Date }, 'days')

[Jenny_Hislop]

Kamille_Parks:

DATETIME_DIFF(TODAY(), {Due Date }, 'days')

Thanks for your reply Kamille. I used your formula and it populated the number of days until it’s due. I’m still not able to get the automations to work though. Do I need to have a field titled today’s date? Is it able to just pull today’s date without th field? When I get rid of the space after due date in formula the formula no longer works, so for some reason there has to be a space there. Im now tryin gto do an automation to find records daily and send an email in grid view with all tasks that are due in 3 days or less. The automation tests correctly each step of the way, but won’t send the email and it doesn’t allow me to see or click on grid view like the airtable articles show it should.

[Kamille_Parks]

Jenny_Hislop:

Do I need to have a field titled today’s date?

No

Jenny_Hislop:

Is it able to just pull today’s date without th field?

Yes. Anywhere in Airtable’s UI when you can filter dates, you can filter for when a date is “today”.

Jenny_Hislop:

When I get rid of the space after due date in formula the formula no longer works, so for some reason there has to be a space there

That means the name of your field has a trailing space. You can fix that by renaming the field.

Jenny_Hislop:

Im now tryin gto do an automation to find records daily and send an email in grid view with all tasks that are due in 3 days or less.

This is seems like a different prompt. The original post made it seem like you wanted an email for each record. If you want to send a list of matching records then the approach in your screenshots is sort of correct except:

Jenny_Hislop:

The automation tests correctly each step of the way, but won’t send the email and it doesn’t allow me to see or click on grid view like the airtable articles show it should.

Your steps are out of order. You are essentially trying to ask Airtable to email you a list of records, but the order you have your steps is “send email, then generate a list”.

As for why the email won’t send, I notice the spelling of your username here doesn’t match the email address you entered (“Hislop” vs “hisop”). Is it possible you made a typo?

[Jenny_Hislop]

Kamille, this is so articulate and helpful, thanks. I was able to get the email sent to me by switching email and records and fixing the typo, however, when the email sent it’s not filtering by the 2 conditions I have 1. needs to just be the editorial calendar 2. needs to be only tasks due in 3 days or less. For some reason it’s showing me all calendars and taks due in March.

[Kamille_Parks · Accepted solution]

Your screenshot doesn’t show the “Calendar Type” column so I can’t verify if that filter is/isn’t working. Your “Due in is less than or equal to 3” filter is working: both -86 and -33 are less than 3.

If the time difference between Due Date and “today” should be positive for future events, you need to adjust your {Due In} formula to reverse the position of TODAY() and {Due Date }.

Always be sure to retest the steps (including the steps that come before it) when troubleshooting Automations. That means whenever you change your formula (or other fields) you need to retest both the Find Records and the Send Email steps (if you were using a record-based trigger like “when record matches conditions” you would need to retest the trigger too). Again, Automation trigger/steps are sequential so always retest starting from the top and going down.

[Jenny_Hislop]

Thanks so much, Im not sure why it wasn’t filtering out the other calendars, but when I switched the formula so overdue tasks are negative its now filtering all correctly via email. Thank you!