Tag a record based on date range

[jimmydaly]

I'd like to create an automation to "tag" each record based on the date it was created. I have a table in the same base (let's call it Table 2) with "Start Date" and "End Date" fields, plus a tag for that date range. So when the new record is created, I'm trying to figure how to take the date/time it was created in Table 1, compare against Table 2, then add the correct tag in Table 1. I'm struggling to figure this out. Any help would be great appreciated.

[Erin_Alter]

is there a reason you wouldn't just use the "Created time" field-type?

[AlliAlosa]

Here’s one approach:

First, create a new formula field on Table 1, and use the following formula:

IF({Date Field}, VALUE(DATETIME_FORMAT({Date Field, 'YYYYMMDD')))

Next, go to Table 2 and create two formula fields with the same formula above - one pointed at the {Start Date} field and one pointed at the {End Date} field.

This will give you translate your date fields into number values that you can use in a find records step in an automation.

The automation could be triggered based on conditions - when the date created is not empty (of course, if you’re using the actual created time field type this will always be true) and the link to your Tag table is empty. In other words, it will run when you haven’t yet assigned it a tag, but know the date by which to search by.

Then you can use the find records step in the automation to find the tag on Table 2 based on conditions. The conditions would be where the number value of the date created is greater than or equal to the number value of the start date, and less than or equal to the number value of the end date.

Finally, use the update record step to link the tag found to the record on Table 1.

I hope that all makes sense! Happy to explain further.

[jimmydaly]

Very helpful thank you! Would this work with time of day as well? Example: On or after April 1, 2024 at 12pm ET

[AlliAlosa]

Sure thing - just adjust the formulas to format the date as 'YYYYMMDDHHmm'.

Make sure you have all the date fields configured the same way in terms of time zone settings. Otherwise you may get some wonky results 😇

[jimmydaly]

Do I understand correctly that I'll have to create a new automation for each tag? I can do this conditionally, but will need 12 automations for 12 tags?

[AlliAlosa]

No, you should only need one automation. When I get to my computer I can send a screenshot of what the setup should look like. Or in the interim if you can send a screenshot of what you have so far I can point you in the right direction 🙌

[AlliAlosa]

Here's an example of what your automation should look like 🙂