How to Make an Availability Schedule in Excel (with Easy Steps)

Get FREE Advanced Excel Exercises with Solutions!

You can find a number of ready-to-use templates online, but creating a customized template that fits your needs can be even more beneficial. You can customize your template by adding columns, formatting, and functionality that are most important to you, such as calculating total hours worked or highlighting certain shifts. As your needs evolve, making your own template can help you better understand how Excel works. Making Excel schedule templates can save you time and improve your productivity. In this article, we’ll show you how to create an availability schedule in Excel.


Watch Video – Make an Availability Schedule in Excel


Overview of How to Make an Availability Schedule in Excel


How to Make an Availability Schedule Template in Excel: Step-by-step Procedures

Here, we are showing you a step-by-step procedure to make an efficient availability schedule template in Excel. You can use this template or you can also follow these steps to make a similar type of template according to your need.

1st Step: Add Necessary Header Information Prior to Your Availability Scheduling Table

To begin with, add some header information, such as the Scheduling table title, Company name, Jurisdiction area, Office location, etc. to give it a formal outlook and set proper applicability.

Necessary Header Information Prior to the Table is Added


2nd Step: Add a Validation Period to Your Availability Scheduling

Consequently, add the Schedule start date from which this schedule will be applicable along with the date when this schedule will expire. You can also mention the duration of this schedule.

Validation Timeline is Added


3rd Step: Add a Calendar to Match Weekly Days and Dates Easily

Optionally, you can add a display calendar to your Excel worksheet to easily get the associated dates with the weekdays during this one-week period, whenever you need.

To do that, first, you need to click on Add-ins under the Developer tab. A new window will pop up. Inside the window, click on STORE. Then inside the search bar type “calendar” and press ENTER. From the search result, click on the Add button beside Mini Calendar and Date Picker.

Adding Daily Calendar

A mini calendar will be visible on your worksheet. Resize and place it anywhere suitable for gaining consistency in the look.

Mini Daily Calendar is Added


4th Step: Build a Skeleton of Your Availability Scheduling Table

After that, create a table, which is going to accommodate your scheduling information. You need to specify the number of rows and columns and draw the table accordingly. You can also merge cells inside any rows or columns to give the table a more organized and suitable outlook.

Skeleton of the Scheduling Table is Built


5th Step: Add Necessary Headings to Each Column

After you are done with building the table skeleton, you need to give the necessary headings to the columns you just added to the table. You can add as many columns as you want to accommodate necessary information inside the table. But do not flow the table with unnecessary columns which can damage the integrity of the table.

Necessary Column Headings are Added


6th Step: Add Necessary Data to Each of the Rows

The row number is equal to the number of people for whom the scheduling is applicable. Add as many people as you need and fill the rows with corresponding information according to the columns.

Necessary Rows with Corresponding Information are Added


7th Step: Generate a VBA Worksheet Event to Improve Readability

Finally, you can generate the following VBA code, which will automatically turn the cell color into red whenever an employee is not available for any particular day. Also, if that person is somehow available, the cell will be colorless automatically.

Now, to write this VBA Code, first launch the Visual Basic Editor by clicking on Visual Basic under the Developer tab.

Opening Visual Basic Window

Then, double-click on the sheet name inside which you want to write the code.

Writing Space for the Generated Code

Finally, inside the worksheet event, you can write the code to get your desired output (If you are using VBA for the first time, you may need to add the Developer tab inside the ribbon Excel).

Code for Highlighting the Unavailable Employees

Code Syntax:

Private Sub Worksheet_Change(ByVal Target As Range)
If LCase(Target.Value) = "no" Then
Target.Interior.ColorIndex = 3 'Red color
Else
Target.Interior.ColorIndex = xlNone 'No color
End If
End Sub

Code Breakdown:

Private Sub Worksheet_Change(ByVal Target As Range)

This is a worksheet event procedure that runs whenever a cell on the worksheet is changed.

If LCase(Target.Value) = "no" Then

This line uses the LCase function to convert the value in the target cell to lowercase, and checks if it is equal to “no”.

Interior.ColorIndex = 3 'Red color

If the value in the target cell is “no”, this line sets the cell interior color to red.

Else

If the value in the target cell is not “no”, this line begins the Else block.

Interior.ColorIndex = xlNone 'No color

This line sets the cell interior color to no color (i.e., the default color) if the value in the target cell is not “no”.

End If

It ends the If statement.

End Sub

This line ends the worksheet event procedure.

To make this code work, you need to type “No” inside the corresponding cell depending on employee unavailability. The cell will automatically turn red and if that employee is somehow available later, just replace the “No” with “Yes” and the cell will return to its colorless condition.

The code is written in such a way that, whether you type, “NO”, “No”, “nO” or “no” it will convert the letters into lowercase, recognize them, and color the cell.

Read More: How to Make a Schedule for Employees in Excel


Things to Remember

  • Determine the purpose of the schedule and what information needs to be included in it. This will help you decide on the structure and layout of the schedule.
  • Make sure to include all important details in the schedule, such as shift duration, work type, employee name, and date. This will ensure that the schedule is accurate and useful.

Download Practice Workbook


Conclusion

By following the steps outlined in this article, you can learn how to make a customized availability schedule in Excel that meets your needs, includes all necessary information, and helps you to stay organized, productive, and on track. If you have any questions or comments, please feel free to leave them in the comment section below.


Related Articles


<< Go Back to Make Schedule in Excel | Excel for Business | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Nafis Soumik
Md. Nafis Soumik

Md. Nafis Soumik graduated from Bangladesh University of Engineering & Technology, Dhaka, with a BSc.Engg in Naval Architecture & Marine Engineering. In January 2023, he joined Softeko as an Excel and VBA content developer, contributing 50+ articles on topics including Data Analysis, Visualization, Pivot Tables, Power Query, and VBA. Soumik participated in 2 specialized training programs on VBA and Chart & Dashboard designing in Excel. During leisure, he enjoys music, travel, and science documentaries, reflecting a diverse range... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo