Create a field that is 6 months after the last date entry in that row

[Nikiska]

Hi Everyone,

I am sure this is a common question, but I can't seem to find it... 

When staff join they have a 3 month/6month and 9 month review. They then have their Annual review (which is always 1 year after their 3 month review). They then have a mid term review and an annual review. My question is...

Is there a script/formula that takes the last date entry in Person A's row and creates a date that is 6 months after it so I can then run an email automation 2 weeks prior to that date to remind Person A  their review is coming up. 

In my example below (DATE FORMAT IS EUROPEAN) As you can see Person B has been employed longer so they are further down the chain they have just had their Y2 annual review and so their Y2 mid term would be 6 months from then (ie 22/7/2024).
Person A's however is still in Y1 and has just had their mid term so they are due their Annual review on 6/5/2024.

So each row will be a different person who will have different dates as they will be at different stages... I know the long way round whereby I created a hidden formula field for each Y1 AR/Y1 MTR/Y2 AR/Y2 MTR and run automations from there but I was just wondering if i could somehow create a field that automatically adds 6 months onto the last date entered in that row!! I can do the rest 🙂

 @TheTimeSavingCo , @Alexey_Gusev , @ScottWorld 

[TheTimeSavingCo]

Hm, I don't think we can create a field that automatically adds 6 months like that I'm afraid.  It'd need to be a formula field the same as the hidden ones you've already created, and we can't create fields with formulas via scripting afaik

[Alexey_Gusev]

Hi,

DATEADD(
IF({Y2 MTR},{Y2 MTR},
IF({Y2 AR},{Y2 AR},
IF({Y1 MTR},{Y1 MTR},
IF({Y1 AR},{Y1 AR},
IF({9 months},{9 months},
IF({6 months},{6 months},
IF({3 months},{3 months},
{Start Date}))))))),
6,'month')

in short, use formula to create, add the rest.
If you have rows with empty dates including Start Date and want to avoid #ERRORs, wrap in IF({Start Date},  *whole formula*  )

[Nikiska · Accepted solution]

Oh wow @Alexey_Gusev , thank you for that, I just needed someone to provide me with the foundations to start me in the right direction so I could then build on that formula to create the below which works perfectly! Thank you so much for your help:)

IF({Start Date}, 
DATETIME_FORMAT(

IF({3 month supervision}=BLANK(), DATEADD({Start Date}, 3, 'months',{3 month supervision}), 
IF({6 month Supervision}=BLANK(), DATEADD({3 month supervision}, 3, 'months',{6 month Supervision}), 
IF({9 month Supervision}=BLANK(), DATEADD({6 month Supervision}, 3, 'months',{9 month Supervision}), 
IF({Y1 AR}=BLANK(), DATEADD({9 month Supervision}, 6, 'months',{Y1 AR}), 
IF({Y1 MTR}=BLANK(), DATEADD({Y1 AR}, 6, 'months',{Y1 MTR}), 
IF({Y2 AR}=BLANK(), DATEADD({Y1 MTR}, 6, 'months',{Y2 AR}), 
IF({Y2 MTR}=BLANK(), DATEADD({Y2 AR}, 6, 'months',{Y2 MTR}),
IF({Y3 AR}=BLANK(), DATEADD({Y2 MTR}, 6, 'months',{Y3 AR}), 
IF({Y3 MTR}=BLANK(), DATEADD({Y3 AR}, 6, 'months',{Y3 MTR}), 
IF({Y4 AR}=BLANK(), DATEADD({Y3 MTR}, 6, 'months',{Y4 AR}), 
IF({Y4 MTR}=BLANK(), DATEADD({Y4 AR}, 6, 'months',{Y4 MTR}), 
IF({Y5 AR}=BLANK(), DATEADD({Y4 MTR}, 6, 'months',{Y5 AR}), 
IF({Y5 MTR}=BLANK(), DATEADD({Y5 AR}, 6, 'months',{Y5 MTR}) 

))))))))))))), 'DD MMMM YY'))