Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Aug 13, 2024 01:17 PM
I'm reaching out for your expertise. My sample table has three date fields: Joined, Separated, and Date Rejoined. It also has two formula fields: Prior # of Years from Joined date to Separated and Total Years. I need your helpto correct the formula for calculating the total years of membership.
I am trying to calculate the total number of years they have been members. I can do that, but the problem is that when they join, separate, and rejoin, we don’t give them credit for the years they were gone/separated. Here is the formula I used for Total years. I’m sure my formula must be wrong for the “total years including prior years".
DATETIME_DIFF(TODAY(), {Joined},'days')/365 - {Prior # of Years from Joined date to Separated}
But for members who have never separated, it gives me NaN for prior and total. Prior years' formula is:
DATETIME_DIFF(Separated, Joined,'days')/365
Lastly, for members who joined, separated, and never rejoined, it gives me the wrong total. But I have worked on this for a week now and can not figure it out. So, the total years is correct, but only if they have separated and rejoined.
Solved! Go to Solution.
Aug 13, 2024 02:44 PM
Oh I did not type out an exact formula by any means, this would do it though:
Aug 13, 2024 01:22 PM
This is easily handled by an if statement! Pseudocode would be:
IF [ (Rejoined date exists),
THEN (Calculate time between separated and joined, calculate time between rejoined and now, sum, these two values)
ELSE (Calculate time between separated and joined)]
Aug 13, 2024 02:20 PM
Thank you for the quick reply. I copied and pasted it, and it says it is an invalid formula. Suggestions? Did I need to make changes to any part of it?
Aug 13, 2024 02:44 PM
Oh I did not type out an exact formula by any means, this would do it though:
Aug 13, 2024 03:11 PM
Wow, it worked perfectly. Thank you so much. I need to study formulas way more. As you can tell, I am an absolute newbie.