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

Topic Labels: Automations Data Formulas
Solved
Jump to Solution
493 3
cancel
Showing results for
Search instead for
Did you mean:
6 - Interface Innovator

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 🙂

1 Solution

Accepted Solutions
6 - Interface Innovator

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'))``````

3 Replies 3
18 - Pluto

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

12 - Earth

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*  )

6 - Interface Innovator

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'))``````