Sanjida Mehrun Guria
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.
Download Practice Workbook
You can download the worksheet for practice from here.
Step-by-Step Procedures to Create Calendar with Time Slots in Excel
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.
Read More: How to Create an Event Calendar in Excel (with Easy Steps)
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.
Read More: How to Create Project Management Calendar in Excel
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.
Read More: How to Create a Weekly Calendar in Excel (3 Suitable Ways)
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)
- Lastly, use the Autofill tool to copy this formula all over the column.
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.
Read More: How to Make a Vacation Calendar in Excel (with Detailed Steps)
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. Learn more about Excel from ExcelDemy.
