Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Feb 28, 2024 07:54 AM
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
Solved! Go to Solution.
Feb 29, 2024 04:01 AM - edited Feb 29, 2024 01:35 PM
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'))
Feb 28, 2024 06:44 PM
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
Feb 28, 2024 07:38 PM - edited Feb 28, 2024 08:28 PM
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* )
Feb 29, 2024 04:01 AM - edited Feb 29, 2024 01:35 PM
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'))