Help

Re: Formulas by row?

Solved
Jump to Solution
1076 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Beth_BCamps
4 - Data Explorer
4 - Data Explorer

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!

2 Solutions

Accepted Solutions
RdMedia_srl
7 - App Architect
7 - App Architect

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"
Schermata 2023-04-17 alle 00.05.26.png
 
  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.
 
Schermata 2023-04-17 alle 00.08.06.png
 
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....
 

Rodolfo - RdMedia

See Solution in Thread

kuovonne
18 - Pluto
18 - Pluto

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.

kuovonne_0-1681698586898.png

 

kuovonne_1-1681698660482.png



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

kuovonne_2-1681698711800.png

kuovonne_3-1681698742842.png

 

See Solution in Thread

4 Replies 4
RdMedia_srl
7 - App Architect
7 - App Architect

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"
Schermata 2023-04-17 alle 00.05.26.png
 
  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.
 
Schermata 2023-04-17 alle 00.08.06.png
 
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....
 

Rodolfo - RdMedia
kuovonne
18 - Pluto
18 - Pluto

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.

kuovonne_0-1681698586898.png

 

kuovonne_1-1681698660482.png



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

kuovonne_2-1681698711800.png

kuovonne_3-1681698742842.png

 

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?

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!