New Contributor

# 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

=IF((AND(AA4<=5)),"1Month",AA4*1)

Thanks

7 Replies

# Re: formula to calculate notice period

This is the way your IF formula ought to look like

=IF(AA4<=5,"1Month",AA4*1)

# Re: formula to calculate notice period

Thank you for simplifying the formula.  Do you know how I can limit the answer to a maximum of 12 on the aa4*1?

# Re: formula to calculate notice period

Perhaps

``=IF(AA4<=5,"1Month",MIN(12,AA4*1)))``

# Re: formula to calculate notice period

Yes that worked perfectly thank you @Sergei Baklan

# Re: formula to calculate notice period

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”?

Kind regards
Chris

# Re: formula to calculate notice period

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