{"id":31685,"date":"2023-11-01T14:59:47","date_gmt":"2023-11-01T13:59:47","guid":{"rendered":"https:\/\/www.myexcelonline.com\/?p=31685"},"modified":"2024-03-22T18:07:00","modified_gmt":"2024-03-22T17:07:00","slug":"create-a-yearly-leave-record-for-employees-in-excel","status":"publish","type":"post","link":"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/create-a-yearly-leave-record-for-employees-in-excel\/","title":{"rendered":"How to Create A Yearly Leave Record for Employees in Excel – The Easy Way"},"content":{"rendered":"

\"How<\/a>
\nManaging employee leave records is an important aspect of human resource management for any company. Having a well-organized and comprehensive leave record in Excel can help businesses keep track of employee absences and maintain transparency. In this article, we’ll guide you through the process of how to create a yearly leave record for employees in Excel. The final spreadsheet<\/a> would look something like this:<\/p>\n

\"How<\/a><\/p>\n

Let\u2019s explore these methods!<\/p>\n

Download the Excel Workbook below to follow along and understand How to Create A Yearly Leave Record for Employees in Excel \u2013<\/h4>\n

Download excel workbook<\/span>YearlyLeaveRecord1.xlsx<\/span><\/a><\/p>\n

Planning Your Spreadsheet<\/span><\/strong>
\nBefore diving into Excel, it’s essential to plan how you want to structure your employee’s yearly leave record.<\/p>\n

Employee Information<\/strong>: Decide on the employee details you want to include, such as employee name, ID, department, or contact information. In our case we\u2019ll use the employee names.<\/p>\n

Leave Types<\/strong>: Identify the types of leave you want to track, such as annual leave, sick leave, and unpaid leave.<\/p>\n

Open Excel<\/strong> and create a new workbook<\/strong>.<\/p>\n

In the first row of your spreadsheet, set up the headers:<\/p>\n

– Employee Name<\/strong>
\n– Leave Type<\/strong> (Annual Leave, Sick Leave, and Unpaid Leave)<\/p>\n

You can customize these headers based on your organization’s needs.<\/p>\n

\"How<\/a><\/p>\n

In cell E2<\/strong>, input the date 1\/1\/2024<\/strong>.<\/p>\n

Click and drag<\/strong> the square on the bottom-right of the cell to apply this date format to the rest of the columns until you reach the end of the year (12\/31\/2024.) This will lead you to column NF.<\/p>\n

\"How<\/a><\/p>\n

\"How<\/a><\/p>\n

Highlight<\/strong> all of the dates. Then, right-click to select Format Cells.<\/strong><\/p>\n

\"How<\/a><\/p>\n

From the Format Cells Menu, click on Custom<\/strong> and select \u201cd<\/strong>\u201d to show the day of the month.<\/p>\n

\"How<\/a><\/p>\n

At this point, we would like to make the columns more compact for ease of use. Highlight the columns from E to NF<\/strong>\u00a0then right-click<\/strong>. Select Column Width<\/strong>, type in 3<\/strong> and click OK<\/strong>.<\/p>\n

\"How<\/a><\/p>\n

As you can see, the columns are now more compact.<\/p>\n

\"How<\/a><\/p>\n

Using the WEEKDAY Function<\/span><\/strong>
\nNext up, we would like to mark the weekends (or the days when the employees are not working) on our calendar and fill the those cells with the color red.<\/p>\n

Right-click to Insert<\/strong> a new row above<\/strong> the dates.<\/p>\n

Select cell E2<\/strong>, enter the formula<\/a> =WEEKDAY(E3)<\/strong><\/p>\n

\"How<\/a><\/p>\n

Note: In Microsoft, Saturdays are bound to 7, Sundays are 1, Mondays are 2, etc. The number on cell E2 is a 2 because January 1st is on a Monday.<\/em><\/p>\n

Drag the formula<\/strong> to the end of the year.<\/p>\n

\"How<\/a><\/p>\n

Highlight cells E4 to NF13<\/strong>. Go up to Conditional Formatting<\/strong> and select New Rule<\/strong>.<\/p>\n

\"How<\/a><\/p>\n

Choose \u201cUse a formula to determine which cells to format.<\/strong>\u201d
\nEnter this formula: =E$2:N$2=6<\/strong> (6 represents Saturday)<\/p>\n

Select Format<\/strong>.<\/p>\n

Go to Fill<\/strong> and select the color red<\/strong>.<\/p>\n

\"How<\/a><\/p>\n

Repeat the same steps for Sunday. This time, the formula will be: =E$2:N$2=7<\/strong><\/p>\n

Now that we have the weekends marked off, we can right-click row 2<\/strong> and select Hide<\/strong>.<\/p>\n

\"How<\/a><\/p>\n

Click here<\/a> to learn more about Excel Date and Time Functions!<\/p>\n

Using the COUNTIF Function<\/span><\/strong>
\nAt this stage, we will use the COUNTIF formula to tally the amount of sick leaves, annual leaves, and unpaid leaves an employee uses throughout the year.<\/p>\n

Example: Kevin Malone uses a Sick Leave<\/strong> on the 2nd\u00a0of January<\/strong>. HR will jot down January 2nd with a simple \u201cS<\/strong>\u201d and the formula will automatically add 1 to his Sick Leave tally.
\n\"How<\/a><\/p>\n

Select cell B4<\/strong>, and enter the formula: =COUNTIF(E4:NF4,\u201dA\u201d)<\/strong><\/p>\n

\"How<\/a><\/p>\n

Drag the formula down for the rest of the employees.<\/p>\n

\"How<\/a><\/p>\n

Use the same formula for Annual Leaves, and Unpaid Leaves. Only this time, using the letters \u201cS<\/strong>\u201d and \u201cU<\/strong>\u201d as distinctions.<\/p>\n

=COUNTIF(E4:NF4,\u201dS\u201d)<\/strong>
\n=COUNTIF(E4:NF4,\u201dU\u201d)<\/strong><\/p>\n

Click here<\/a> to learn more about the COUNTIF Function!<\/p>\n

Formatting the Months<\/span><\/strong>
\nNow let\u2019s mark the months to make the data sheet more manageable.<\/p>\n

Select the cell just above the first day of January.<\/p>\n

Enter =E3<\/strong><\/p>\n

\"How<\/a><\/p>\n

Right-click<\/strong> and go to Format<\/strong>.<\/p>\n

On the Number<\/strong> tab, select Custom<\/strong> and type in \u201cmmm<\/strong>\u201d<\/p>\n

\"How<\/a><\/p>\n

This will give you the first 3 letters of the month.<\/p>\n

Click and drag<\/strong> the square on the bottom-right of cell E6<\/strong> to apply the formula to the rest of the months.<\/p>\n

\"How<\/a><\/p>\n

\"How<\/a><\/p>\n

Click here<\/a> to master Custom Formatting in Excel!<\/p>\n

Freeze Pane<\/span><\/strong>
\nOne of the neat things about Excel is you can utilize Freeze Pane to ensure that the header row or column remains visible, even when scrolling. This is particularly helpful when you need navigate and input data in a large spreadsheet.<\/p>\n

Select cell E3<\/strong>, go to the View<\/strong> tab, and select Freeze Panes<\/strong>.<\/p>\n

\"How<\/a><\/p>\n

Now, you\u2019ll be able to scroll all the way across the spreadsheet.<\/p>\n

\"How<\/a><\/p>\n

Conclusion<\/strong>:
\nThere you have it! Creating an employee yearly leave record format in Excel will help you become more organized in your workforce management. Make sure to use these steps for your 2024 Employee Leave Record!<\/p>\n

Click here<\/a> to access Microsoft\u2019s tutorial on How to Use the COUNTIF Function!<\/p>\n","protected":false},"excerpt":{"rendered":"

Managing employee leave records is an important aspect of human resource management for any company. Having a well-organized and comprehensive leave record in Excel can help businesses keep track of employee absences and maintain transparency. In this article, we’ll guide you through the process of how to create a yearly leave record for employees in […]<\/p>\n","protected":false},"author":1,"featured_media":31710,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"cos_headline_score":0,"cos_seo_score":0,"cos_headline_text":"","cos_headline_has_been_analyzed":false,"cos_last_analyzed_headline":[],"om_disable_all_campaigns":false},"categories":[325,73,20],"tags":[329,1429,1425,1186,108,1428,1427,62,1426],"acf":[],"_links":{"self":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/31685"}],"collection":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/comments?post=31685"}],"version-history":[{"count":0,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/31685\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media\/31710"}],"wp:attachment":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media?parent=31685"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/categories?post=31685"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/tags?post=31685"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}