Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

formula problem

Topic Labels: Formulas
Solved
Jump to Solution
1428 4
cancel
Showing results for 
Search instead for 
Did you mean: 
BarbT
4 - Data Explorer
4 - Data Explorer

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. 

1 Solution

Accepted Solutions
Alyssa_Buchthal
8 - Airtable Astronomer
8 - Airtable Astronomer

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

See Solution in Thread

4 Replies 4
Alyssa_Buchthal
8 - Airtable Astronomer
8 - Airtable Astronomer

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? 

Alyssa_Buchthal
8 - Airtable Astronomer
8 - Airtable Astronomer

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.