Hello,
I'm having a little difficulty getting a tenure formula working.
Essentially, I need it to calculate the number of years between the start date and today's date to the nearest tenth, but rounded down.

So with a start date of 2022/08/01 and today's date 2023/08/01, the tenure should be 1.0.

I calculate this by doing (Today() - Start_Date__c )/365 essentially, but with leap years and rounding, things get a little tricky.

I tried doing varying modifications to the calculation such as using FLOOR, dividing by 365.25, and/or subtracting 0.05 to force a round down, but things get thrown off when it's a matter of a day difference near the tenure promotion date, and the more (or less) years difference between the start date and today, depending on the formula used, the more it gets thrown off.

Any suggestions how this can be achieved? Essentially it shouldn't hit the whole number until it's the actual date of their tenure, and no sooner.

I'm almost thinking I may have to move this out of a simple formula field and move it to some Flow calculation looking for leap years in between the start date and today.

Any help would be truly appreciated. Thanks.
Mathew


 

Hello,
I'm trying to build out a field that counts days while a condition is true. It's intended to count tenure while the customer is in an active membership.

We do have variables for the start date and end date of the membership which counts tenure of that specific membership, but we can't just use that because if the customer discontinues their membership and picks up a new membership at a later time, they'll start at the tenure they previously had. Another issue is they can have multiple memberships active at the same time so it can't be a tally of ALL membership tenures. 

Essentially I need it to be this psuedocode:

AT 12:00AM, IF ActiveMembership >0 THEN TenureDayCounter=TenureDayCounter+1.

Does this make sense? Hoping for some pointers here. 

Thanks in advance for your help. 

-Mathew