How to Create a Maternity Leave Calculator in Excel – 6 Easy Steps

This is an overview of the maternity leave calculator. It calculatesÂ  Statutory Maternity Pay (SMP) and Statutory Maternity Leave (SML).

Step 1 – Create a Dataset

• Enter Employee Name, Joining Date, and Basic Salary in columns B, C, and D.
• Enter data.

• Expected Due Date:Â  date of birth.
• Latest Date to Qualify for SMP: apply the Statutory Maternity Pay by adding 41 weeks before the child’s birth.
• Qualify for SMP?: To qualify for the additional SMP, the employee must have a minimum 1-year service.
• Deadline to Notify for Maternity Leave: 15 weeks before the child’s birth.
• Earliest Date to Commence: 11 weeks before maternity leave.
• Maternity Pay: Total maternity payment based on the basic salary.
• Payment Before Leave: Half the total payment.
• Payment After Leave: Half the total payment.

Read More: How to Create Leave Tracker in Excel

Step 2 – Utilize the Data Validation Feature to Import Data from a Table Array

• Select B5 and go to the Data tab â†’ Data Validation in Data Tools.

• Select Settings â†’ Choose List in Allow â†’ in Source, select the table used in Step 1.
• Click OK.

• Click the Dropdown icon in B4 to see the employee list.

Step 3 – Merge the VLOOKUP and the IFERROR Functions to Display Data

Combine the VLOOKUP and the IFERROR, functions.

• Enter the following formula in C5,
`=IFERROR(VLOOKUP(B5,'Employee List'!\$B\$4:\$D\$9,2,0),"")`
• Press Enter or Tab.

• Enter the following formula in D5,
`=IFERROR(VLOOKUP(B5,'Employee List'!\$B\$4:\$D\$9,3,0),"")`
• Press Enter.

Formula Breakdown:
• VLOOKUP(B5,’Employee List’!\$B\$4:\$D\$9,3,0) looks for the 3rd row of the data in B5 in B4:D9.
• IFERROR(VLOOKUP(B5, ‘Employee List’!\$B\$4:\$D\$9,3,0),””) It returns a blank cell if there’s missing data or an error.

Step 4 – Combine the IF Function and Excel Formulas to Set the SMP Conditions

Use the IF function and Excel formulas to determine the maternity leave timeline.

• Enter the formulas and press Enter or Tab:
• InÂ  D9:
`=D7-7*41`

subtracts 41 weeks from the Expected Date in D7.

• In D10:
`=IF(D9>C5, "Qualified", "Not Qualified")`

determines if the condition is True or False and returns Qualified or Not Qualified.

• In D11:
`=D7+7*15`

• In D12:
`=D7-7*11`

Step 5 – Merge the IF and the IFERROR Functions to Calculate the Maternity Pay

To determine the payment:

• Enter the following formulas and press Enter.
• In D14:
`=IFERROR(IF(D10="Qualified",D5*52, D5*26),"")`

• In D15:
`=IFERROR(D14/2,"")`

• In D16:
`=IFERROR(D14/2,"")`

Formula Breakdown
• IF(D10=”Qualified”, D5*52, D5*26) multiplies 52 by the Basic Salary if the employee is Qualified. Otherwise, by 26.
• IFERROR(IF(D10=”Qualified”, D5*52, D5*26),””) returns blank cells if the reference cells are blank.

Step 6 – Track Maternity Timeline and Analyze the Maternity Leave Calculator

In our final step, we will input the necessary dates and employee names and track their leave timeline. Further, we will visually analyze and test our data. The following instructions will guide you to do so.

• Enter the due date in D7.
• Go to B5 and select the employee.
• Observe the GIF.

<< Go Back to Excel Employee Leave Tracker | Excel HR TemplatesÂ |Â Excel Templates

Get FREE Advanced Excel Exercises with Solutions!
Yousuf Khan

Yousuf Khan has been working with the ExcelDemy project for over one year. He has written 47+ articles for the ExcelDemy site. He also solved a lot of user problems with ExcelDemy Forum. He has interests in Excel & VBA, Desktop and mobile applications, and projects & templates. He completed his graduation and post-graduation in Information Technology from Jahangirnagar University. Currently, he works as a VBA & Excel Content Developer in ExcelDemy projects, writing unique and informative content... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF