Provide share if possible excel formula to calculate ESB with below criteria: Service less than or equal to 5 Years, calculation will be 0.5 month Salary Per Year Service more than 5 Years, calculation will be 1 month Salary Per Year Compute additional working days and months above in the formula as well This formula have some missing criteria =SUMPRODUCT((DATEDIF($G16,G17,"y")>{0,5})+0,DATEDIF($G16,G17,"y")-{0,5},$G21*{0.5,0.5}) + IF(DATEDIF($G16,G17,"ym")>0, $G21 * DATEDIF($G16,G17,"ym")/12, 0) + IF(DATEDIF($G16,G17,"md")>0, $G21 *

If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.

Excel formula to calculate ESB (End Service Benefit)

Thread starter Shafayat
Start date Dec 14, 2023

Shafayat

New Member

Dec 14, 2023

Provide share if possible excel formula to calculate ESB with below criteria:

Service less than or equal to 5 Years, calculation will be 0.5 month Salary Per Year
Service more than 5 Years, calculation will be 1 month Salary Per Year
Compute additional working days and months above in the formula as well

This formula have some missing criteria =SUMPRODUCT((DATEDIF($G16,G17,"y")>{0,5})+0,DATEDIF($G16,G17,"y")-{0,5},$G21*{0.5,0.5}) + IF(DATEDIF($G16,G17,"ym")>0, $G21 * DATEDIF($G16,G17,"ym")/12, 0) + IF(DATEDIF($G16,G17,"md")>0, $G21 *

Last edited by a moderator: Dec 14, 2023