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

This article illustrates how to create the employee leave record in the proper format in excel. If you are working in the HR department, you will most probably need to keep track of the employee leave record. Fortunately, Excel can help you to do that efficiently. You will need a properly formatted worksheet for the employee leave record to do that. This article will help you to create one to serve that purpose.


Download Sample Workbook

You can download the free template from the download button below.


Steps to Create Employee Leave Record Format in Excel

Follow the steps below to create your employee leave record in the desired format in excel.

๐Ÿ“Œ Step 1: Set Leave Types and Months

  • First, you need to make a list of the leave types that you allow to your employees. Using a short code for each type will be convenient to choose. The following picture will give you an example.

  • Then, you need to create a list of months. You will record the leaves taken by each employee on each of these months.
  • Letโ€™s start with the first month of 2022. Enter the dates 1-Jan-2022 and 1-Feb-2022 in cells E5 and E6 Then select cells E5:E6 and drag the Fill Handle icon. Excel will understand that you are trying to create a series with one month increment. So, you will see the month of the date increase. Drag the icon until it reaches the desired month. Here, we will drag until 1-Dec-22 for illustration.

  • After that, you will get the following result.

  • Now, keep the dates selected. Then, press CTRL+1 to open the Format Cells dialog box. Next, use mmm-yyyy as the Custom Number Format for the dates. Then, press OK.

  • After that, you will get the following result.


๐Ÿ“Œ Step 2: Set Months Drop-Down with ComboBox

  • Now create a new worksheet (LeaveRecord). Assume you want to enter the employee names and their IDs in column B and column C respectively.

employee leave record format

  • Next, go to the Developer tab and select Insert >> Combo Box (Form Control).

  • Then, drag the mouse around cell C4 as required to resize the Combo Box. You can choose another location for it if needed.

  • After that, you will see the Combo Box inserted as follows. Put the cursor on the small circles and drag your mouse to resize the Combo Box if needed.

  • Now, right-click on the Combo Box and select Format Control.

  • Then go to the control tab in the Format Object dialog box. Next, click on the upward arrow in the Input range and select all the months generated earlier in the DatesLeaves After that, use the upward arrow in the Cell link to select cell B3 in the current worksheet (LeaveRecord).

  • Then, go to the Properties tab and mark the radio button for โ€œDonโ€™t move or size will cellsโ€. Next, press OK.

  • Now, you can select any month of the list using the dropdown. Cell B3 will show the order of the selection.

employee leave record format


๐Ÿ“Œ Step 3: Generate Dates and Days for Selected Month

  • Next, enter the following formula in cell C3. The INDEX function returns the date from the list of months based on the order of selection in cell B3.
=INDEX(DatesLeaves!E5:E16,LeaveRecord!B3)

  • Then, format cell C3 as d-mmm. After that, enter the following formula in cell D4.
=C3

  • Next, select cells B3:C3 and change the font color to white.

  • Now, enter the following formula in cell D5. Here theย TEXT function converts the value to a text in the specific number format.
=TEXT(D4,"DDD")

employee leave record format

  • After that, apply the following formula in cell E4. Here theย EOMONTH function returns the serial number of the last day of the month before or after the specified number of months.
=IF(D4="","",IF(EOMONTH($C$3,0)>=D4+1,D4+1,""))

employee leave record format

  • Then, copy the formula in cell D5 to cell E5 by dragging the fill handle icon.

  • Next, change the text alignment of cells D4:D5 to vertical. You can also do that from the Alignment tab in the Format Cells (CTRL+1) dialog box.

  • Now select cells E4:E5 and apply the same alignment. Then drag the fill handle icon up to column AH to copy the formulas. After that, you will get the following result.

employee leave record format


๐Ÿ“Œ Step 4: Create Drop-Down to Record Leaves

  • Next, select the range D6:AH10. Then, select Data >> Data Validation (ALT+A+V+V).

employee leave record format

  • After that, select List as the Validation criteria. Then, click on the upward arrow in the Source field and select the sort codes for the leave types. Click OK after that.

  • Now, you can choose the leave type using the dropdowns in any of those cells.

employee leave record format in excel


๐Ÿ“Œ Step 5: Enter Formula to Count Leaves

  • Then, copy the table for Leave Types and paste it as Transpose onto cell AI4. Next, enter the following formula in cell AI6. Now, drag the fill handle icon to cell AM6 and then to cell AM10. The COUNTIF function counts the cells based on the specified criteria.
=COUNTIF($D6:$AH6,AI$5)

employee leave record format

  • Next, enter the following formula in cell AN6. After that, copy the formula down to the cells below.
=SUM(AI6:AM6)

employee leave record format


๐Ÿ“Œ Step 6: Format Cells to Hide Zeros

  • Now, select the range AI6:AN10. Then, select Conditional Formatting >> New Rule from the Home tab.

  • Next, select the โ€œFormat only cells that containโ€ rule. Then select Cell Value >> equal to and enter a 0. Click on Format after that.

  • Now change the font color to white. Then, click OK.

  • Then, click OK again.

  • Finally, start recording the leaves taken by your employees to see the following result

Employee leave record format in excel


Things to Remember

  • Be careful while entering the formulas with mixed references to avoid erroneous results.
  • You can include more rows to record data for more employees.

Conclusion

Now you know how to create the employee leave record in the desired format in excel. Do you have any further queries or suggestions? Please let us know in the comment section below. You can also visit our ExcelDemy blog to explore more about excel. Stay with us and keep learning.

Md. Shamim Reza

Md. Shamim Reza

Hello there! This is Md. Shamim Reza. Working as an Excel & VBA Content Developer at ExcelDemy. We try to find simple & easy solutions to the problems that Excel users face every day. Our goal is to gather knowledge, find innovative solutions through them and make those solutions available for everybody. Stay with us & keep learning.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo