How to Create Maternity Leave Calculator in Excel (With Easy Steps)

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 the 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).

How to Create Maternity Leave Calculator in Excel (With Easy Steps)

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.

• Similarly, open a new sheet and add the following headers.
• 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.

Read More: How to Create Leave Tracker in Excel

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,

• After that, press the Enter or Tab keys.

• Similarly, type the following formula in D5,

• Again, press the Enter button.

Read More: How to Create Employee Monthly Leave Record Format in Excel

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,

• Here, we subtract 41 weeks from the Expected Date in D7.

• Afterward, type this in cell D10,

• The function determines if the condition is where True or False and returns Qualified or Not Qualified respectively for either case.

• Meanwhile, in D11,

• Finally, put the formula in D12,

Read More: How to Track Employee Vacation Time in Excel

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,

• Next, in D15,

• Lastly, in cell D16,

Read More: How to Create Employee Leave Record Format in Excel

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.

Download Practice Workbook

Download this workbook and practice while going through the article.

Conclusion

In conclusion, we have discussed some easy steps to create a maternity leave calculator in Excel. Please, leave any further queries or recommendations in the comment box below.

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