# How to Create Maternity Leave Calculator in Excel

Get FREE Advanced Excel Exercises with Solutions!

Microsoft Excel is a powerful software. We use Excel formulas and functions in our education, business, and workplaces. Moreover, using Excel, HR can track employees’ maternity leave timelines and payments through Maternity Leave Calculator. With this in mind, we will learn 6 easy steps to create a maternity leave calculator in Excel. Before diving into the steps, let’s take a look at the overview of the Excel maternity leave calculator. It is as self-explanatory as it thoroughly calculates the Statutory Maternity Pay (SMP) and Statutory Maternity Leave (SML).

## Step-by-Step Procedures to Create Maternity Leave Calculator in Excel

In this article, we will initially utilize Excel VLOOKUP, IFERROR, IF functions, and Excel formulas to design the maternity leave calculator. Furthermore, we will access the Data Validation feature to help us build the calculator. Lastly, we will track the timeline and payment gateway. Therefore, let’s jump into the steps.

### Step 1: Introduce Dataset with Proper Parameters

The first step is as easy as it sounds. It aims to include the necessary parameters for the purpose of building the maternity leave calculator. Therefore, let’s add the following headers to our dataset.

• Initially, introduce Employee Name, Joining Date, and Basic Salary in columns B, C, and D respectively.
• Further, populate the headers with the correct employee information. • Expected Due Date: The expected date of birth.
• Latest Date to Qualify for SMP: You can apply for Statutory Maternity Pay by joining 41 weeks prior to the child’s birth.
• Qualify for SMP?: To qualify for the additional SMP, the employee ought to have a minimum 1-year service record.
• Deadline to Notify for Maternity Leave: You have to notify 15 weeks prior to the child’s birth.
• Earliest Date to Commence: 11 weeks prior to maternity leave.
• Maternity Pay: Total maternity payment based on the basic salary.
• Payment Before Leave: Half of the total payment.
• Payment After Leave: Half of the total payment. ### Step 2: Utilize Data Validation Feature to Import Data from Table Array

The second step aims to access the Data Validation feature to display the data list in the worksheet. Go through the steps to do so.

• Firstly, select B5 and go to the Data tab → Data Validation icon in the Data Tools group. • There, select Settings → Choose List in the Allow box → and in the Source box, select the table of Step 1.
• Finally, hit OK to close the dialog box. • Lastly, clicking the Dropdown icon in B4 will display the employee list. ### Step 3: Merge VLOOKUP and IFERROR Functions to Display Data

The objective of the third step is to combine the VLOOKUP and IFERROR functions to show the corresponding columns along the specified column. The VLOOKUP function searches for a given data in the leftmost column of a table and returns the data in the same row from a specified column. Alternatively, the IFERROR function returns the specific data that we input for an error condition. Let’s follow the instructions below to create a combination formula.

• To begin with, write the following formula in C5,
`=IFERROR(VLOOKUP(B5,'Employee List'!\$B\$4:\$D\$9,2,0),"")`
• After that, press the Enter or Tab keys. • Similarly, type the following formula in D5,
`=IFERROR(VLOOKUP(B5,'Employee List'!\$B\$4:\$D\$9,3,0),"")`
• Again, press the Enter button. Formula Breakdown:
• VLOOKUP(B5,’Employee List’!\$B\$4:\$D\$9,3,0) looks for the corresponding 3rd row of the data in B5 from the range B4:D9 of the Employee
• IFERROR(VLOOKUP(B5, ‘Employee List’!\$B\$4:\$D\$9,3,0),””) however, returns a blank cell if finds any missing or error data.

Read More: Employee Monthly Leave Record Format in Excel (with Easy Steps)

### Step 4: Combine IF Function and Excel Formulas to Set SMP Conditions

In this step, we will join the IF function and Excel formulas to determine the maternity leave timeline. The IF function takes a given condition and returns True or False outputs. Follow the below procedure.

• First, type the following combined formulas in their respective cells and press the Enter or Tab keys.
• In cell D9,
`=D7-7*41`
• Here, we subtract 41 weeks from the Expected Date in D7. • Afterward, type this in cell D10,
`=IF(D9>C5, "Qualified", "Not Qualified")`
• The function determines if the condition is where True or False and returns Qualified or Not Qualified respectively for either case. • Meanwhile, in D11,
`=D7+7*15` • Finally, put the formula in D12,
`=D7-7*11` ### Step 5: Merge IF and IFERROR Functions to Calculate Maternity Pay

The objective of this step is to combine the IF function and the IFERROR function to determine the payment. See the below procedure.

• To begin with, write the following formula in the respective cells and tap Enter.
• In cell D14,
`=IFERROR(IF(D10="Qualified",D5*52, D5*26),"")` • Next, in D15,
`=IFERROR(D14/2,"")` • Lastly, in cell D16,
`=IFERROR(D14/2,"")` Formula Breakdown
• IF(D10=”Qualified”, D5*52, D5*26) multiplies 52 with the Basic Salary if the employee is Qualified. Otherwise, multiply 26.
• IFERROR(IF(D10=”Qualified”, D5*52, D5*26),””) returns blank cells if the reference cells are blanks.

Read More: Employee Leave Record Format in Excel (Create with Detailed Steps)

### Step 6: Track Maternity Timeline and Analyze 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.

• Firstly, input the due date in D7.
• Later, go to B5 and select the employee.
• See the below gif for a better understanding. • Thus, we calculate the SMP and SML in our calculator.

Read More: How to Calculate Leave Balance in Excel (with Detailed Steps)

## Conclusion

In conclusion, we have discussed some easy steps to create a maternity leave calculator in Excel. Not to mention, our ExcelDemy website shows various simple problem-solving methods like this. Please, leave any further queries or recommendations in the comment box below.

## Related Articles Yousuf Khan

Hello! This is MD Yousuf Khan. I am a graduate & post-graduate in Information Technology from Jahangirnagar University, Bangladesh. Currently, I am writing articles for ExcelDemy. I am an independent, self-motivated person with enthusiasm to learn new things, and always try to do my best in any work assigned to me.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  