Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Airtable Community
- Discussions
- Ask A Question
- Formulas
- A unique formula for each record

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Solved

Jump to Solution

0
884
2

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Comment Post Options

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jan 12, 2023 04:48 PM

I would like to be able to have a different formula in each record, rather than one formula applied to every record.

My table is a task list. The Due Date of each task (record) is relative to the Due Date (date field) of the Final Task. Task A is due 1 day before the Final Task is due, Task B is due 5 days before the Final Task is due, etc.

Is there a way to input a formula into each record's field that would calculate from a field in another record within the same table? Like if I put "-1 day" into the field (I don't know what type of field could do this) of Task A, it would display October 30, 2023 if Final Task's Due Date was October 31, 2023.

The best I've come up with so far is a short text field where I write "1 day before" "5 days before" etc and then plug in the dates in the date field once I've decided Final Task's Due Date. However, this means if I change Final Task's Due Date, I have to manually change all records' Due Dates.

I can't find anything like this anywhere. It's probably not possible with the Formula field. But it seems like a function that would be useful for project management scheduling. So I suggest we figure it out!

Solved! Go to Solution.

1 Solution

Accepted Solutions

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jan 12, 2023 05:39 PM

Solution/workaround:

I created a Number field "Timeline" to put in the days before/after the Final Task Due Date I want the dates on the other tasks, ie -1 for one day before, 0 for same day, 7 for a week after.

I created a Formula field and created this formula:

DATEADD("03/02/2023", ({Timeline}+1), "days")

"03/02/2023" being the Final Task due date. I had to add the "+1" because it wasn't calculating as expected. For 1 it was staying the same date, 0 was one day before, 2 was one day after, etc. So I just had to adjust by 1.

So now I can hide the Timeline field and just see my dates. And probably rename the Formula field "Due Date: March 3" for context.

If I need to change the Final Task due date for this project, I will change it in the Formula field. And once this project is over and I'm using the same table of tasks for the next project, I input that project's due date instead.

Guess I worked this out for myself but I'll leave it up in case anyone else is searching for this function.

2 Replies 2

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jan 12, 2023 05:11 PM

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jan 12, 2023 05:39 PM

Solution/workaround:

I created a Number field "Timeline" to put in the days before/after the Final Task Due Date I want the dates on the other tasks, ie -1 for one day before, 0 for same day, 7 for a week after.

I created a Formula field and created this formula:

DATEADD("03/02/2023", ({Timeline}+1), "days")

"03/02/2023" being the Final Task due date. I had to add the "+1" because it wasn't calculating as expected. For 1 it was staying the same date, 0 was one day before, 2 was one day after, etc. So I just had to adjust by 1.

So now I can hide the Timeline field and just see my dates. And probably rename the Formula field "Due Date: March 3" for context.

If I need to change the Final Task due date for this project, I will change it in the Formula field. And once this project is over and I'm using the same table of tasks for the next project, I input that project's due date instead.

Guess I worked this out for myself but I'll leave it up in case anyone else is searching for this function.