How to Create Calendar with Time Slots in Excel (With Easy Steps)

Microsoft Excel is a powerful tool to create a calendar. Even when it comes to making it dynamic with time slots, Excel is the best option. In this article, we will learn how to create a calendar with time slots in Excel with some easy steps. Simply follow them to create your own.

How to Create Calendar with Time Slots in Excel: with Easy Steps

We need a time slot along with a calendar to mark it with certain events, reminders or any other pre-determined occasion. Therefore, let’s follow the steps below to create the calendar with time slots. Here, we will create a calendar for 1 week for ease of understanding. But you will get the monthly calendar in the downloadable excel file.

Step 1: Prepare Starting Time and Time Slots Setup

In the beginning, we need to create a 24-hour timetable and a time interval table separately. They will work as the determinants for the calendar.
For this, insert the Start Time for 24 hours and Time Interval in Columns B and D respectively.

Step 2: Create an Outline for the Calendar

Now, we will create the outline for the calendar. Before this, we need to prepare another table where we will insert the values from the dataset. For this, follow the steps below.

First, create a table with Start Time, Time Interval and Week Start Date titles.

Then, create another table below inserting 7 days’ names.
Along with it, insert another column titled TIME as shown below.

Step 3: Apply Data Validation Based on Starting Time and Time Slots

At this stage, we will apply Data Validation to the first table above the calendar. It will help to align it with the original dataset.

First, select cell B5.
Then, go to the Data tab and click on the Data Validation icon under the Data Tools group.

Next, choose List from the Allow option in the Data Validation window.

After this, refer to the cell range B5:B28 as the Source from the Dataset sheet.

Similarly, select cell C5 and insert the following setting in the Data Validation window.

Lastly, press OK.
As a result, you will see that the cells are now data validated and both have Arrows to open respective valued lists.

Along with it, insert your required date as the Week Start Date in cell D5.

Step 4: Insert Dates in the Calendar

At this stage, we will insert dates into the calendar. For this, go through the steps below.

First, insert this formula in cell C8 to get the first date.

=D5

Then, right-click on it and choose Format Cells from the Context Menu.

Afterward, choose your preferred format in the Type box of the Date section under the Number tab.

Then, hit on OK to close the Format Cells window.
Now, insert this formula in cell D8 to get the consecutive date.

=C8+1

Accordingly, use the AutoFill tool to get all the dates for the whole week like this.

Step 5: Insert Time Slots in the Calendar

In this last step, we will insert the time slots based on the dataset that we prepared initially. To do the task, follow the process below.

First, select cell B9 and insert this formula to get the Start Time.

=B5

Then, insert this formula in cell E5 to count the interval of time.

=LEFT(C5,3)

In this formula, the LEFT function fetches the left-sided text from the string. Along with this, 3 is given as the num_chars argument because we will consider the text up to 3 places.

Next, apply this formula in cell B10 to get the consecutive time.

=B9+TIME(0,$E$5,0)

Here, the TIME function calculates the time that is added with the previous cell B9. cell E5 is given as an absolute cell reference to keep the time interval constant.

Lastly, use the AutoFill tool to copy this formula all over the column.

Note: Initially you will get the #VALUE! error after applying this formula. Don’t worry, it will be removed after you provide the date and interval from the data validation lists of Start Time and Time Interval.

Final Output

Finally, we are at the final stage of creating a calendar with time slots. Let’s see how it works.

Initially, choose a time from the list of Start Time.

Then, choose any interval of time from the list of Time Interval.

Finally, you will get the final calendar with time slots as shown below.

For your flexibility, you can change the start time and interval and make the calendar more dynamic according to your preference.

Download Practice Workbook

You can download the worksheet for practice from here.

Calendar with Time Slots.xlsx

Conclusion

That’s all for today. In this article, we tried to explain to you some easy steps on how to create a calendar with time slots in Excel. Let us know your feedback on this.

<< Go Back to Excel Calendar Templates | Excel Templates

Get FREE Advanced Excel Exercises with Solutions!