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.

Open a new sheet and add the following headers:

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.

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.

Download Practice Workbook

Download the workbook and practice.

Maternity Leave Calculator.xlsx

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

Get FREE Advanced Excel Exercises with Solutions!

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