Help

Re: A unique formula for each record

Solved
Jump to Solution
549 0
cancel
Showing results for 
Search instead for 
Did you mean: 
tish
5 - Automation Enthusiast
5 - Automation Enthusiast

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!

1 Solution

Accepted Solutions
tish
5 - Automation Enthusiast
5 - Automation Enthusiast

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.

See Solution in Thread

2 Replies 2
tish
5 - Automation Enthusiast
5 - Automation Enthusiast

Maybe an automation? (I've tried- can't figure it out but maybe) Like if I set all the dates as far apart as I want them, then set an automation that when I change the Due Date field of Final Task record, it automatically changes the Due Date field in the rest of the records by the same number of days. Possible?

tish
5 - Automation Enthusiast
5 - Automation Enthusiast

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.