Formulas by row?

[Beth_BCamps]

New here!
Is it possible to set a different formula in each row of a column? For example, I am looking to set a due date in one of our rows (which is one of our tasks) to automatically calculate a date 2 months before a start date of a program. The second row, is another task and has another due date (7 days before the start date). The start date is the same for both. Is this possible? Thanks!

[RdMedia_srl · Accepted solution]

Hi, you can do something like this:

create two separate formula fields (that you can hide), one for each due date calculation, and then using a third field to display the final due date based on the task type. Here's how you can do it:

1. Create a formula field called "Due Date - 2 Months" and enter the formula to calculate the due date 2 months before the start date. For example, if your start date is in the field "Start Date," you can use the following formula:

DATEADD({Start Date}, -2, 'months')

1. Create another formula field called "Due Date - 7 Days" and enter the formula to calculate the due date 7 days before the start date. For example, you can use the following formula:

DATEADD({Start Date}, -7, 'days')

Then you create a "Single select field" like this where you choose you range for the "due date"

1. Create a third field called "Due Date" (or anything you prefer) and use a formula to display the due date based on the "Type due date". You can use the following formula:

IF({Type Due date}= "7 days", {Due Date 7 days}, {Due Date 2 month})

this is the finale structure, you can hide the 2 formula field "Due Date 2 month" and "Due Date 7 days" and just use the "Type due date" select field to calculate you range.

 

Of course you can add more "select field", like "1 month" and you will have to add a new formula field (Due date 1 month) and modify the "Due date" formula.

Hope this helps....

[kuovonne · Accepted solution]

It isn't possible to have different formulas for different cells in a column. It is possible to have a formula that performs different calculations based on record data.

One way to get different {due dates} is to have fields that indicate the number and units of the lead time.

You could also use a single-select field with the lead time:

[Beth_BCamps]

Thanks for this! My company's task list has about 30 tasks and all have different timelines for due dates. This would then create 30 tabs right?

Tasks are done and then triggers someone else to do another task, etc etc. I'd love to automate some of these due dates rather than manually entering them. Any suggestions?

[Beth_BCamps]

Thank you so much! I Think this could work. Any chance you can send me the formulas via message so I can copy and paste them? I am assuming I can name the columns whatever I'd like? Thanks again!