Combining conditions and returning a future date

Platform discussions
, , ,
1

Newbie to monday.com, here. Can someone help me figure out an automation or formula to make this work?

Baseline info:

Rows:
Hire Date
90 day eval
6 month bonus
12 month bonus

Columns:
Date
Time Frame (Dropdowns include 90 days, 6 months, 1 year, n/a)
Dependent on (Hire Date)
Formula

I would like the formula to return, in the 90 day eval row, the date that is 90 days from the date in the Hire Date row. I created the Time Frame dropdowns so I could exclude this formula from applying to anything other than the date value I need. I will need to also create (and combine) formulas for the 6 month and 12 month lines.

So, if the dropdown value in Time Frame is “90 days”, AND Dependent On is the “hire date” row, THEN populate the Date with a date 90 days from the date reflected in the Date field on the New Hire row.

Maybe I need to specify something more clearly in the Dependent On row, so it knows to look at the date? I’m absolutely confused.

Here are a few formulas I have tried with no luck:

IF(AND({Date})>0,{Time Frame#Labels},“90 days”),(ADD_DAYS({Date},90))

IF({Time Frame#Labels},“90 days”),(ADD_DAYS({Date},90),“”)

IF({Date}),AND({Time Frame#Labels},“90 days”),(ADD_DAYS({Date},90),“”)

Hopefully there’s a Monday genius out there who can help me figure this one out. Thanks much!

2

Hi Amber,

Natively a formula column can only reference columns from its own row. You can’t tell it to look for a value in another row.

But it can be done with a 3rd-party app called Advanced Formula Booster.

This app doesn’t use the formula column and can write formula results to any kind of column. Plus it can read and update any row in the board, even from other boards.

In this case, here is the formula you would use:
2024-04-07_17-43-38

It reads: Date of item #2 is equal to the date of item #1 plus 90 days.

You simply create the formula, then add an automation like this one:
2024-04-07_17-45-54
 
 
 
Here is the result:
row2date
 
 
 
----------------------------------------------------------------------- 

Now, this assumes Hire Date is always row #1 and 90 day eval always row #2.
What if the rows get shuffled?
You could create a formula that doesn’t take for granted the position of each row.

2024-04-07_17-28-27
2024-04-07_17-28-271115×133 63.5 KB

  • 1: Looks for the row number of the “Hire Date” item in {GroupItems.Name} which is the list of the names of the items and stores it under [SourceRow]. Here its position is 1.
  • 2: Does the same for the “90 day eval” row and stored it under [TargetRow]. Here its position is 2.
  • 3: Same as the previous formula: adds 90 days to the date in row #1 and writes it to the date column in row #2, except this time, 1 and 2 have been calculated and could be anything depending on the order of your rows.

If you need any help with the Advanced Formula Booster, feel free to write me in private or use the in-app ticket system.

3

@GCavin , thank you so much! This is fantastic information!