Skip to main content
Solved

adding to a formula

B
Forum|alt.badge.img+5

My table includes three date fields: Joined, Separated, and Date Rejoined, along with two formula fields: Prior Years and Total Years of Service.

I’ve now added two additional fields: Date Separated 2 and Prior Years 2, to account for members who have separated and rejoined more than once. Some members join, then separate, later rejoin, and eventually separate again. I need to accurately track their total time as active members, excluding any periods when they were separated.

The original formula for Total Years of Service worked well—until I had to include a second separation date.

Here is the formula that worked originally but now I need to take into account the second separated date.

If anyone can rewrite this for me, I would be grateful. I am not good at formulas.

 

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

 

Best answer by TheTimeSavingCo

Hmm does this look right?

 

IF(
  AND({Date Separated 2}, {Date Rejoined}, {Separated}, {Joined}),
    DATETIME_DIFF({Separated}, {Joined}, 'days') +
    DATETIME_DIFF({Date Separated 2}, {Date Rejoined}, 'days'),
  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

If you foresee people separating and joining a lot more often then you may want to consider restructuring your base to handle that, but if they generally only join and leave two or three times you should be fine extending the formula

View original
    Did this topic help you find an answer to your question?

    TheTimeSavingCo
    Forum|alt.badge.img+28

    Hmm does this look right?

     

    IF(
  AND({Date Separated 2}, {Date Rejoined}, {Separated}, {Joined}),
    DATETIME_DIFF({Separated}, {Joined}, 'days') +
    DATETIME_DIFF({Date Separated 2}, {Date Rejoined}, 'days'),
  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

    If you foresee people separating and joining a lot more often then you may want to consider restructuring your base to handle that, but if they generally only join and leave two or three times you should be fine extending the formula

    Adam, Consultant @ thetimesaving.company
    B
    Forum|alt.badge.img+5
    • BarbT
    • Inspiring
    • April 8, 2025

    Worked perfectly. Thanks for the advice regarding restructuring. I think the max would be three times they might separate. If that happens I think I can follow your formula to adjust it again. While I have your ear, any suggestions on how to back the base up? I do download CSV files but wondering if there is a way to back up the whole base easier.. I have a lot of info here for our volunteer fire dept. and would hate to have it lost by accident or operator error., me being the operator.

    TheTimeSavingCo
    Forum|alt.badge.img+28

    Hmm, Airtable takes snapshots of your base pretty often, so you can just restore from there if you need to (Docs)

    •  

    If you’re worried about losing the entire base, maybe you could duplicate the base into another workspace once a week or something?  Even if you delete the entire base by accident you can still restore it within 7 days too (Docs)

    Adam, Consultant @ thetimesaving.company

    Reply


    Most helpful members this week

    Terms & Conditions