I have an associates table with a Date/Time field named subscribed_on
Now I would like to add a second field named expire_on that has to be the first of Jan of the year following the one the associate asked to subscribe.
I was thinking to use an equation field but I found no way to build the new date (the classic EXCEL DATE() function).
There are functions like getDatexxx but nothing to build a date I miss something?
Here is a bit of a round about way of doing it, but it works. Create 3 additional fields as below:
RENEW Year is an equation as below. Make sure you pick NUMERIC even though you are using dates, or it doesn’t work. This gives you the following year.
The Text Formula simply builds out the 1/1/XXXX string:
Then RENEW DATE turns that string into a useable date, using a conditional rule:
A bit messy, and maybe someone else knows how to streamline this, but its functional and doesn’t require any special code or tricks …
Thank you @LeighBerre96926 I will try it, even if I am afraid that in this way we cannot compare the values.
i.e. chek if in the next 30 days someone needs to be renewed…
Yes you can. The final field is a fully usable date field that will be able to be reported on, filtered, or used as a trigger in tasks just like any other date field.
Just set up a view filtered by renew date is today, before today or in the next 30 days…
