formula problem

[BarbT]

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. 

[Alyssa_Buchthal]

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

[BarbT]

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? 

[Alyssa_Buchthal · Accepted solution]

Oh I did not type out an exact formula by any means, this would do it though:

IF(AND({Date Rejoined}, {Separated}, {Joined}), DATETIME_DIFF({Separated}, {Joined}, 'years') + DATETIME_DIFF(TODAY(), {Date Rejoined}, 'years'), IF(AND({Joined}, {Separated}), DATETIME_DIFF({Separated}, {Joined}, 'years')), IF({Joined}, DATETIME_DIFF(TODAY(), {Joined}, 'years'), BLANK()))

Also, just as a heads-up, because you have your increments in years, you're going to get integers only, but if you want it in years but with appropriate decimals, use this instead: 

IF(AND({Date Rejoined}, {Separated}, {Joined}), DATETIME_DIFF({Separated}, {Joined}, 'days') + DATETIME_DIFF(TODAY(), {Date Rejoined}, 'days'), IF(AND({Joined}, {Separated}), DATETIME_DIFF({Separated}, {Joined}, 'days'), IF({Joined}, DATETIME_DIFF(TODAY(), {Joined}, 'days'), BLANK())))/365

[BarbT]

Wow, it worked perfectly. Thank you so much. I need to study formulas way more. As you can tell, I am an absolute newbie.