Can Airtable calculate a date for a record automatically, based on a date for a different record? Example: on my todolist, I have a field for “send newsletter” with a date of 12/1/2019. I want Airtable to calculate the deadline for writing content (14 days before 12/1/) and the deadline for proofreading content (7 days before 12/1). The WORKDAY function seems close, but it looks like that yields one value based on another value in the same field. A slightly different situation.
Welcome to the community, Paul! :slightly_smiling_face:
Through some table trickery, there are ways to have records operate based on on other records. However, I’m not sure how to make that trickery work in your situation. I’m guessing that not all records in your todo list would need this cross-record comparison, and that’s one of the things that pretty much all of these tricks do: they operate on ALL records, not just a select few.
I can think of some possible ways to isolate such behavior between records that are specially marked in one way or another, but even that concept has its flaws. How do you specify which task is the reference point for the others? Generally that means more data to manually enter. What if you need to have several task groups like this? Trying to further isolate the behavior so that one group’s date comparisons don’t get mixed up with another’s would dramatically increase the complexity, if it’s even possible at all.
My suggestion would be to split off such multi-task projects into their own tables. For example, make a newsletter project table, with each record tracking the production of a single newsletter. You would have a deadline date field where you enter your final deadline for a given newsletter. Other fields could then use formulas to calculate other related deadlines (writing, proofreading, etc) based on that master deadline. You could then link that newsletter project record back into your primary task table, perhaps using some other formulas along the way so that you can easily see from your main task list what the current status is on the linked newsletter project.