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