Forum Discussion

Kayletab's avatar
Kayletab
Copper Contributor
Mar 25, 2020

formula to calculate notice period

Hi,   I am currently using the following formula to calculate notice period but the maximum years is 12 how can i add a clause to the formula to ensure the number calculated is no more than 12   ...
excel
Formulas and Functions
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Mar 26, 2020

Kayletab 

Perhaps

=IF(AA4<=5,"1Month",MIN(12,AA4*1)))
Kayletab's avatar
Kayletab
Copper Contributor
Mar 26, 2020

Yes that worked perfectly thank you SergeiBaklan 

    • ChrisM1990's avatar
      ChrisM1990
      Copper Contributor
      Sep 18, 2020
      Hello,

      The formula returns “1 month” if there is 0-5 years’ service and returns the figure 6-12 if there is over. For example, 7 years’ service would return “7”.

      Is there any way, should the value be greater than 5, to return this with “weeks” after the value? For example, “7 weeks” instead of just “7”.

      Kind regards
      Chris
    • ChrisM1990's avatar
      ChrisM1990
      Copper Contributor
      Sep 18, 2020
      Hi Sergei,

      Thank you very much for your help with this formula.

      I’ve tried it on a spreadsheet for work but where the employee has over five years service, the formula returns a number. For example, if the employee has up to 5 complete years of service, the formula returns “1 month”. However, if the employee has, for example, 7 complete years’ of service, the formula returns “7”.

      Would it be possible for the formula to return “7 weeks”?

      I am extremely grateful for any help you can give

      Kind regards
      Chris