How to Create Automatic Schedule Generator for Free in Excel

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.

How to Create Automatic Schedule Generator in Excel: with Easy Steps

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 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 2^th to June 18^th.

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.

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.

Free Template: Ready to Use

You can use a ready-made template that we have included in the 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.

Download Practice Workbook

You can download and practice the dataset that we have used to prepare this article.

Creating Automatic Schedule Generator.xlsx

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.

<< Go Back to Roaster Templates | Excel Templates

Get FREE Advanced Excel Exercises with Solutions!