microsoft excel vacation tracking spreadsheet

#MICROSOFT EXCEL VACATION TRACKING SPREADSHEET HOW TO#

Select Window group > Freeze Panes drop-down menu > Freeze Panes tool. To do this, select cell F8 first and go to the View tab.

Finally, use the Freeze Panes tool to freeze the columns in the A:E range and freeze the rows in the 1:7 range.

Then, format the cells with effective colors to make the data more visible.

Firstly, select cell AK8 and type in the formula below.

In this phase, we’ll count the total number of vacations taken this month. Step 05: Counting Vacation Days and Vacations in Hand

#MICROSOFT EXCEL VACATION TRACKING SPREADSHEET HOW TO#

Read More: How to Create Employee Monthly Leave Record Format in Excel However, cells containing V reflect the defined highlighting.

Choose the formatting as in the image below. Then, in the Text That Contains dialog box, write down V in the Format cells that contain the text box.Then, select Conditional Formatting > Highlight Cells Rules > Text that Contains. Fourthly, select the cells in the F8:AJ17 range.A portion of vacation marking is like below. Thirdly, place the vacation days of all individuals in their respective cells.That means we could just input this text only. You can see a drop-down list containing just V. Select List from the drop-down list in the Allow box. At this point, the Data Validation dialog box opens.Go to the Data tab and select Data Tools > Data Validation. Firstly, select cells in the F8:AJ17 range.For this, we have to input vacation days into cells in the F8:AJ17 range. The main objective of this article is to create an employee vacation time tracker in Excel. Step 04: Inserting Vacation in Cells as V The cells under the columns of the Sun are highlighted in red. Now, use the Fill Handle tool to expand this formatting in cells in the F8:AJ17 range.It returns to the New Formatting Rule dialog box.After that, select the Fill tab > Choose your preferred color and click on OK. Then, the Format Cells dialog box opens.Select cell C4 and input the formula below. After that, we have to set the starting date for this month.We can easily see that the month’s name is automatically input into cell F2 after changing the Sheet Name. As we wanna make the employee vacation sheet for the month of Jan’22. After that, change the name of the sheet to Jan.For example, here we’ve entered the reference of cell A1.

Otherwise, the formula won’t work properly. Note: While typing this formula, make sure to enter any cell references on this sheet.

MID: The MID function uses the text’s position from start to end to extract a specific substring.Īt this point, we can see the name of our Sheet on this cell with 2022.

255: Excel’s maximum word count for the sheet name.

FIND(“]”,CELL(“filename”,A1)) +1: The FIND function will give you the position of ] and we’ve added +1 because we require the position of the first character in the name of the sheet.

CELL(“filename”,A1): The CELL function gets the complete name of the worksheet.

This formula returns the sheet name in the selected cell.

Microsoft excel vacation tracking spreadsheet