Hello SF Experts! I need help coming up with a formula that will calculate the number of hours on an end date by increasing the percentage each year between the start and end dates. Assume the following:
- Start Date = 1/1/2015
- Growth Rate = 4%
- Base Hours = 100
- End Date = 12/31/2018
To get the value I need:
- Subtract the ‘Start Date’ year from the ‘End Date’ year. 2018-2015 = 3
- Multiply hours by growth rate 3 times (since hours grow each year and there are 3 years between start and end date) = 100*1.04*1.04*1.04 = 112.49
- Field value should be 112.49 and should fluctuate based on varying hours, growth rates and start/end dates (would ideally like to pro-rate by month also).
Thanks in advance for your help!
9 answers
Hi Kristy,
Try this - you may need to play about with the growth rate as I can't tell whether you have stored it as a number or percent field:
base_hours__c * ((1 + growth_rate__c) ^ (YEAR(end_date__c) - YEAR(start_date__c)))
'^' (hat) is the EXP function - https://help.salesforce.com/articleView?id=customize_functions_a_h.htm&type=0&language=en_US
Put your own field names in!
Thanks,
Mike.
