Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Create Maternity Leave Calculator in Excel

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.

Overview of 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).


Download Practice Workbook

Download this workbook and practice while going through the article.


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.

Introducing Dataset to Create Maternity Leave Calculator in Excel

  • 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.

Another Dataset to Create Maternity Leave Calculator 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.

Using Data Validation Feature for maternity leave calculator excel

  • 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.

Data Validation Dialog Box

  • Lastly, clicking the Dropdown icon in B4 will display the employee list.

Result of Data Validation


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.

Joining VLOOKUP and IFERROR functions to create maternity leave calculator in Excel

  • Similarly, type the following formula in D5,
=IFERROR(VLOOKUP(B5,'Employee List'!$B$4:$D$9,3,0),"")
  • Again, press the Enter button.

Joining VLOOKUP and IFERROR functions to create maternity leave calculator in Excel

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.

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.

Joining Excel formula and IF function in D9 to create maternity leave calculator in Excel

  • 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.

Joining Excel formula and IF function in D10 to create maternity leave calculator in Excel

  • Meanwhile, in D11,
=D7+7*15

Joining Excel formula and IF function in D11 to create maternity leave calculator in Excel

  • Finally, put the formula in D12,
=D7-7*11

Joining Excel formula and IF function in D12


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),"")

Joining IFERROR and IF functions to create maternity leave calculator in Excel

  • Next, in D15,
=IFERROR(D14/2,"")

Calculate Payment Before Leave

  • Lastly, in cell D16,
=IFERROR(D14/2,"")

Calculate Payment After Leave

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.

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.

Gif Output to Create maternity leave calculator in Excel

  • Thus, we calculate the SMP and SML in our calculator.

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.

Yousuf Khan

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

Leave a reply

ExcelDemy
Logo