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.

Preparing Starting Time and Time Slots

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.

Create Outline for Calendar with Time Slots in Excel

  • 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.

Apply Data Validation Based on Dataset for Creating Calendar with Time Slots in Excel

  • 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.

Apply Data Validation Based on Dataset

  • 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.

Insert Dates in Calendar

  • 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.

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

Read More: How to Create an Event Calendar in Excel

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.


Insert Time Slots in Calendar

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


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.


Insert Time Slots in Calendar

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.

Calendar with Time Slots Excel

  • 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.


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.

Related Articles

<< Go Back to Excel Calendar Templates | Excel Templates

Get FREE Advanced Excel Exercises with Solutions!
Sanjida Mehrun Guria
Sanjida Mehrun Guria

Hello! Welcome to my blog. I have completed my Bachelor in Architecture from Ashanullah University of Science & Technology, Dhaka. I am a passionate, goal-oriented person with an immense thirst for knowledge and an attitude to grow continuously. Besides Architectural work, I find it very enthusiastic to work in Excel blogging. Exceldemy is a platform where I have got the opportunity to flourish my skill in Microsoft Excel and therefore, here I will be posting blogs related to... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF