Apex Code Development You need to sign in to do that
Don't have an account?
Mathew Arcilla Tenure calculation years rounding down
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
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
Hi Mathew,
For the requirement that you have mentioned, you can use the below formula as it was working for me:
FLOOR(
(
(YEAR(TODAY()) - YEAR(Start_Date__c)) * 365
+ (DAY(TODAY()) - DAY(Start_Date__c))
- (IF(MOD(YEAR(TODAY()), 4) = 0, IF(OR(MOD(YEAR(TODAY()), 100) != 0, MOD(YEAR(TODAY()), 400) = 0), 1, 0), 0)
- (IF(MOD(YEAR(Start_Date__c), 4) = 0, IF(OR(MOD(YEAR(Start_Date__c), 100) != 0, MOD(YEAR(Start_Date__c), 400) = 0), 1, 0), 0)))
) / 365.0
)
If the information helps, please mark this as best answer.
Thanks