The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.
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'))