Skip to main content
Solved

formula problem

B
Forum|alt.badge.img+5

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. 

Best answer by Alyssa_Buchthal

BarbT wrote:

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? 


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
View original
1 Attachments
Did this topic help you find an answer to your question?

5 replies

A
Forum|alt.badge.img+13

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

B
Forum|alt.badge.img+5
  • BarbT
  • Author
  • Inspiring
  • 5 replies
  • August 13, 2024
Alyssa_Buchthal wrote:

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


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? 

A
Forum|alt.badge.img+13
BarbT wrote:

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? 


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
B
1 person likes this
  • B
    BarbT
B
Forum|alt.badge.img+5
  • BarbT
  • Author
  • Inspiring
  • 5 replies
  • August 13, 2024
Alyssa_Buchthal wrote:

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

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

B
Forum|alt.badge.img+5
  • BarbT
  • Author
  • Inspiring
  • 5 replies
  • March 28, 2025

You helped me with this  formula-

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

 

Your formula above gives me the total years of service for a member if they join, separate, and then rejoin.

Now I have this problem: they separate a second time (Date Separated2) field name and then rejoin again (Date Rejoined2). Is there a way to work that into the formula above so I can still get the correct total number of years of service?  I added two fields (Date Separated2 and Date Rejoined2, assuming that was necessary. I hope I’m explaining this well enough. Thanks for any help.

Reply


Most helpful members this week

Terms & Conditions