Mohammad Shah Miran
Excel is essential to business analysis because it offers a variety of ways to store and analyze data. In addition, it is a practical, cost-effective, and manageable tool because of the many options and features that are built into it. Apart from that, you can also create charts, and presentations or make a work schedule for your project. In this article, we will discuss how to create an automatic Schedule Generator for free in Excel. So stay with us as we discuss it briefly.
Download Practice Workbook
You can download and practice the dataset that we have used to prepare this article.
3 Steps to Create Automatic Schedule Generator in Excel
Let’s assume we have a dataset, namely “Project Timeline of ABC Multipurpose Bridge“. However, you can use any dataset suitable for you.
Here, we have used the Microsoft Excel 365 version; you may use any other version according to your convenience.
Step 01: Prepare the Dataset
Before delving into our today’s topic, there is some pre-processing work that needs to be done. Those are the sorting of the project’s starting and ending date, task duration calculation, project duration calculation, and so on.
- To begin with, enter the following formula in cell D5.
=MIN(C9:G9)
Here C9:G9 represent the Starting Date of the different task of the given construction project, and we have used the MIN function to accomplish the task.
⚡Formula Breakdown
- MAX(C10:G10) → returns the lowest value in a set of values. Here, the C9:G9 cells represent Starting Date of the project.
- Output → 2-Feb-23
- Similarly, to find the Ending Date of the project, here we will use the MAX function.
- write the following formula in cell F5.
=MAX(C10:G10)
⚡Formula Breakdown
- MAX(C10:G10) → returns the largest value in a set of values. Here, the C10:G10 cells represent the Ending Date of the project.
- Output → 18-Jun-23
- Enter the following formula in cell C11, to find the duration of each task.
=C10-C9
Here C9 and C10 represent the Starting Date and Ending Date of each task respectively.
- Drag the Fill Handle tool from left to right to get the other values.
- To sum up the task durations, write the following formula in cell C5.
=SUM(C11:G11)
Here we use the SUM function and C11:G11 represents the time duration of each task starting from Feb 2th to June 18th.
Read More: How to Create a Project Schedule in Excel (with Easy Steps)
Step 02: Create the Automatic Schedule Generator
Now it’s time to create an automatic schedule generator in your Excel sheet. Before applying the formula by which you will create the generator, we need to input the dates into our workbook.
- Write 2-Feb-2023 and 3-Feb-2023 in cells B12 & B13
- After that, select those two cells and then drag the Fill Handle too from B12 to B148 to get the other dates also.
- See the GIF attached below to get a visual demonstration of it.
- Now, write the following formula in cell D5
=IF(AND($B12>=C$9,$B12<=C$10),"X","")
⚡ Formula Breakdown:
- IF(AND($B12>=C$9,$B12<=C$10),”X”,””) → checks whether a condition is met and returns one value if TRUE and another value if FALSE. Here in the IF function, we have incorporated AND function, and AND($B12>=C$9,$B12<=C$10) is the logical_test argument that compares the value of the B12 cell with C9 and the B12 value with C10 If the value of the B12 cell is greater than or equal to C9 and the B12 value is less than or equal to C10, then the function returns “X” (value_if_true argument) otherwise the cell will be empty (value_if_false argument).
- Output → X
- See the output as given below.
- Following that, drag down the formula in the C12:G148 range and see the total picture of the output as shown below.
Read More: How to Create a Schedule in Excel That Updates Automatically
Similar Readings
- How to Create a Recurring Monthly Schedule in Excel
- Make a Class Schedule on Excel (With Easy Steps)
- How to Make a Daily Schedule in Excel (6 Practical Examples)
- Create Depreciation Schedule in Excel (8 Suitable Methods)
- Schedule Optimization in Excel (Detailed Analysis)
Step 03: Highlight Occupied Days
- Now it’s time to give some aesthetic look to our schedule generator.
- First, select all cells in the C12:G148 range.
- Then go to Home > Conditional Formatting > Highlight Cells Rules > Equal To.
- Subsequently, a dialogue box will pop up.
- Write X in the blank box, then select Light Red Fill with Dark Red Text. Though you can also select any of the options in with box according to your preference.
- Click on OK afterward.
- Now see the output as given below.
Read More: How to Create a Monthly Schedule in Excel (3 Simple Ways)
Free Template: Ready to Use
You can use a ready-made template that we have included in the given Excel file at your disposal. All you need to do is put the starting and ending date of each task in the highlighted cells. And don’t forget to change the dates in B columns according to your project duration.
Practice Section
We have provided a Practice section on the right side of the sheet so you can practice yourself. Please make sure to do it yourself.
Conclusion
In this article, we have discussed how to create an automatic schedule generator for free in Excel. However, If you have any queries, feel free to comment below and we will get back to you soon. Also, you may follow our website, ExcelDemy, a one-stop Excel solution provider to explore more.
Related Articles
- How to Make a Schedule for Employees in Excel (3 Types)
- Make an Availability Schedule in Excel (with Easy Steps)
- How to Make a Work Schedule in Excel (3 Handy Examples)
- Create a Weekly Schedule in Excel (2 Suitable Methods)
- How to Make an Hourly Schedule in Excel (with Easy Steps)
- How to Create a Workback Schedule in Excel (with Easy Steps)
