I have a formula that will look at the start date of a hire, the starting salary and then increment the salary by a referenced percentage upon the anniversary of start date. However, the formula only works for the first anniversary, and I need to update for each successive anniversary. My brain is full. The formula: IF(AND(G$8>=$E14,OR($F14+30>G$8,$F14=0)),IF(G$8-$E14>365,($D14*(1+$B$4))/12,$D14/12),0) Thank you in advance!

gclarke It's my pleasure to have enabled you to learn something. This is my first time to make a request from someone whom I've somehow helped. Could you kindly mark my previous reply as Best Response so that the number of my Best Responses crosses the two-digit line?

Need Help with a Nest Formula | Microsoft Community Hub

6 Replies

Twifoo
Silver Contributor
Nov 06, 2021
gclarke
As I illustrated in the attached version of your file, the formula in H13 is:
=IF(H$8<$F13,0, ($E13/12)*(1+$C$4)^ INT(YEARFRAC($F13,H$8-1,1)))
Annual Increase in Monthly Salary.xlsx70 KB
- gclarke
  Copper Contributor
  Nov 06, 2021
  Twifoo Much gratitude! Always nice to learn something!
  - Twifoo
    Silver Contributor
    Nov 06, 2021
    gclarke
    It's my pleasure to have enabled you to learn something. This is my first time to make a request from someone whom I've somehow helped. Could you kindly mark my previous reply as Best Response so that the number of my Best Responses crosses the two-digit line?
Riny_van_Eekelen
Platinum Contributor
Nov 06, 2021
gclarke In H13, try this:
=IF($F13<=H$8,$E13*(1+$C$4)^INT(DATEDIF($F13,H$8,"m")/12)/12,0)
Copy down and across. I'm not a big fan of the DATEDIF function, but in this case it works just fine.
- gclarke
  Copper Contributor
  Nov 06, 2021
  Riny Thank as well, I appreciate the assistance

Forum Discussion

Need Help with a Nest Formula

6 Replies

Resources