formula to calculate notice period

%3CLINGO-SUB%20id%3D%22lingo-sub-1253927%22%20slang%3D%22en-US%22%3Eformula%20to%20calculate%20notice%20period%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1253927%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20currently%20using%20the%20following%20formula%20to%20calculate%20notice%20period%20but%20the%20maximum%20years%20is%2012%20how%20can%20i%20add%20a%20clause%20to%20the%20formula%20to%20ensure%20the%20number%20calculated%20is%20no%20more%20than%2012%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF((AND(AA4%26lt%3B%3D5))%2C%221Month%22%2CAA4*1)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1253927%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1254028%22%20slang%3D%22en-US%22%3ERe%3A%20formula%20to%20calculate%20notice%20period%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1254028%22%20slang%3D%22en-US%22%3EThis%20is%20the%20way%20your%20IF%20formula%20ought%20to%20look%20like%3CBR%20%2F%3E%3CBR%20%2F%3E%3DIF(AA4%26lt%3B%3D5%2C%221Month%22%2CAA4*1)%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1254638%22%20slang%3D%22en-US%22%3ERe%3A%20formula%20to%20calculate%20notice%20period%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1254638%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F421698%22%20target%3D%22_blank%22%3E%40Abiola1%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20simplifying%20the%20formula.%26nbsp%3B%20Do%20you%20know%20how%20I%20can%20limit%20the%20answer%20to%20a%20maximum%20of%2012%20on%20the%20aa4*1%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1256848%22%20slang%3D%22en-US%22%3ERe%3A%20formula%20to%20calculate%20notice%20period%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1256848%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F595410%22%20target%3D%22_blank%22%3E%40Kayletab%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPerhaps%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DIF(AA4%26lt%3B%3D5%2C%221Month%22%2CMIN(12%2CAA4*1)))%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1256863%22%20slang%3D%22en-US%22%3ERe%3A%20formula%20to%20calculate%20notice%20period%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1256863%22%20slang%3D%22en-US%22%3E%3CP%3EYes%20that%20worked%20perfectly%20thank%20you%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1256901%22%20slang%3D%22en-US%22%3ERe%3A%20formula%20to%20calculate%20notice%20period%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1256901%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F595410%22%20target%3D%22_blank%22%3E%40Kayletab%3C%2FA%3E%26nbsp%3B%2C%20glad%20to%20help%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1683295%22%20slang%3D%22en-US%22%3ERe%3A%20formula%20to%20calculate%20notice%20period%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1683295%22%20slang%3D%22en-US%22%3EHi%20Sergei%2C%3CBR%20%2F%3E%3CBR%20%2F%3EThank%20you%20very%20much%20for%20your%20help%20with%20this%20formula.%3CBR%20%2F%3E%3CBR%20%2F%3EI%E2%80%99ve%20tried%20it%20on%20a%20spreadsheet%20for%20work%20but%20where%20the%20employee%20has%20over%20five%20years%20service%2C%20the%20formula%20returns%20a%20number.%20For%20example%2C%20if%20the%20employee%20has%20up%20to%205%20complete%20years%20of%20service%2C%20the%20formula%20returns%20%E2%80%9C1%20month%E2%80%9D.%20However%2C%20if%20the%20employee%20has%2C%20for%20example%2C%207%20complete%20years%E2%80%99%20of%20service%2C%20the%20formula%20returns%20%E2%80%9C7%E2%80%9D.%3CBR%20%2F%3E%3CBR%20%2F%3EWould%20it%20be%20possible%20for%20the%20formula%20to%20return%20%E2%80%9C7%20weeks%E2%80%9D%3F%3CBR%20%2F%3E%3CBR%20%2F%3EI%20am%20extremely%20grateful%20for%20any%20help%20you%20can%20give%3CBR%20%2F%3E%3CBR%20%2F%3EKind%20regards%3CBR%20%2F%3EChris%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1685164%22%20slang%3D%22en-US%22%3ERe%3A%20formula%20to%20calculate%20notice%20period%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1685164%22%20slang%3D%22en-US%22%3EHello%2C%3CBR%20%2F%3E%3CBR%20%2F%3EThe%20formula%20returns%20%E2%80%9C1%20month%E2%80%9D%20if%20there%20is%200-5%20years%E2%80%99%20service%20and%20returns%20the%20figure%206-12%20if%20there%20is%20over.%20For%20example%2C%207%20years%E2%80%99%20service%20would%20return%20%E2%80%9C7%E2%80%9D.%3CBR%20%2F%3E%3CBR%20%2F%3EIs%20there%20any%20way%2C%20should%20the%20value%20be%20greater%20than%205%2C%20to%20return%20this%20with%20%E2%80%9Cweeks%E2%80%9D%20after%20the%20value%3F%20For%20example%2C%20%E2%80%9C7%20weeks%E2%80%9D%20instead%20of%20just%20%E2%80%9C7%E2%80%9D.%3CBR%20%2F%3E%3CBR%20%2F%3EKind%20regards%3CBR%20%2F%3EChris%3C%2FLINGO-BODY%3E
New Contributor

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
This is the way your IF formula ought to look like

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

@Abiola1 

 

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

@Kayletab 

Perhaps

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

Yes that worked perfectly thank you @Sergei Baklan 

@Kayletab , glad to help

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