# Creating an Excel Schedule Template

## How to Make a Schedule in Excel

Working in a busy accounting office gives me a diverse workload; with problems trying to efficiently plan time it is often important to create a work schedule to provide an overview of workload and therefore be able to plan accordingly. While professional tools like Microsoft Project give you plenty of options to design detailed work plans, the complexity and steep learning curve often are too much for most.

Therefore creating a schedule template within Excel that uses some of the powerful functionality of conditional formatting will provide a simple yet powerful solution. The end result is an easy to use tool that allows tasks to listed providing a simple Gantt chart for a great overview of workload.

The instructions below will guide you step by step on how to create a schedule template in Microsoft Excel:

## Step one – Creating a Basic Schedule Layout in Microsoft Excel.

The first thing to do is decide what information is needed on the schedule. As an example the following information will be incorporated into the design:

- The Task Name
- Start Date
- End Date
- A range of dates – in this case the schedule will display the next 25 days
- Comments – to add details about the task

Start by creating a basic worksheet. Enter the date in column D and then add a simple formula to the subsequent cells that will add one to the prior cell. For example – cell **E1 **has the formula *=+D1+1.*

The format of the worksheet does not matter at this time. The example uses simple formatting, with the dates aligned vertically. Generic data is added so that any formulas and formats applied to the worksheet work as intended.

## Step 2 – Adding Formulas to Create the Excel Schedule

The Gannt part of the schedule will be represented by a ‘bar’ that corresponds to the start date and end date. To accomplish this add a formula in each cell so that it compares the Start Date and End Date of each column to the date of the row – if the date of the row is within the range then the formula will place an x in that cell, otherwise it will leave the cell blank.

The formula in cell D2 is: **=IF(AND(D$1>=$B2,D$1<=$C2),"x","")**

This is a fairly simple formula using the =IF function: =IF(Condition, True, False)

Condition: AND(D$1>=$b2,D$1<=$C2) – this is checking that the date in D1 is greater or equal to the date in B2 and less or equal to the date in C2 – if it is then it will use the ‘True’ result, otherwise it will use the ‘False’ result). Note – the formula uses the $ sign for two different reasons:

- D$1 – this ensures that when the formula is copied down and across, the D will change to the relevant column while the 1 will remain the same.
- $b2 – this ensures that when the formula is copied down and across, the B will remain the same but the 2 will change to the relevant row number.

In the diagram above, the formulas have placed an x in the relevant rows and columns to coincide with the Start and End Dates.

## Step 3 – Using Conditional Formatting to Format the Schedule.

Click thumbnail to view full-size## Using Conditional Formatting in Microsoft Excel:

- Select the area to be conditionally formatted
- On the
**Home**menu on the Microsoft Excel Ribbon click**Conditional Formatting**. - Click on
**New Rule** - Select
**Use a formula to determine which cells to****format**and enter the formula in**Format values where this formula is true** - Click
**Format**to amend the format of the cell (when the above formula returns a true value) – click**OK**to return back once the format has been selected. - Click
**OK**to apply the format.

**Highlighting today’s date**

To make 'today' stand out more a conditional formula can be used to change the format of the cell on row 1 of the worksheet. Using the conditional formatting method (right):

Cells A1 to AC1 (apply this format to the top row only)*Area**:*=IF(A1 = TODAY(), 1, 0) (This will compare every value in the selected range to today’s date – if it is the same it returns TRUE and formats that cell based on the selection. The cell reference after the IF should be the top left cell in the range)*Use formula**:*Use*Format**:***Fill**->**Fill Effects**to create the format.

**Shade weekends**

To break up the schedule and also to provide additional information about work days and weekends conditional formatting can be used to highlight any dates that fall on the weeked. This format will be applied to the entire schedule.

Cells A1 to AC6*Area**:*=IF(AND(A$1 <>"", OR(WEEKDAY(A$1, 2)=6, WEEKDAY(A$1, 2)=7)), 1, 0) (This will return a value if the date in question is a Saturday or Sunday – the WEEKDAY function will convert any date into a number ranging from 1 to 7 – where 6 and 7 represent Saturday and Sunday)*Use formula**:*Use*Format**:***gray**background and**black**font color.

**Highlighting today’s date in schedule section**

The schedule already highlights today's date in row one. Applying a different format to today's date for all rows below is a good visual guide on the schedule.

Cells D1 to AB6*Area:*=IF(D$1 = TODAY(), 1, 0) (This will compare every value in the selected range to today’s date – if it is the same it returns TRUE and formats that cell based on the selection. The cell reference after the IF should be the top left cell in the range )*Use formula:*Use*Format:***Fill**->**Pattern Style**to create the format.

**Replace ‘x’ with blue ‘box’**

The 'x' in the schedule provide a graphical view of the date range, however replacing the 'x' with a solid blue box will create a schedule that is similar to the tradional Gantt chart.

Cells D1 to AB6*Area:*=IF(D1="x", 1, 0) (This will compare every value to ‘x’ – if it is the same it returns TRUE and formats that cell based on theselection. The cell reference after the IF should be the top left cell in the range)*Use formula:*Use*Format:***Fill**and**FONT**color and use the same color for both.

The design of a simple Microsoft Excel schedule is complete. To add more tasks simply copy the last row and paste it into the next row – the conditional formatting will be copied too.

## More by this Author

- 5
Learn how to freeze rows in Microsoft Excel

- 20
Microsoft Excel has become the standard spreadsheet for most business use, but with it's functionality and ease of use the advantages of Excel can be used at home as well.

- 8
Choosing a class is Mass Effect is not easy. Various factors must be considered: are you a beginner? Do you like combat oriented games? are you more into stealth? The chose you make will change the game you play.

## Comments 6 comments

I haven't read this much about Excel since my mandatory high school computer class! Thanks for the detailed instructions in plain English.

Hm, I should make a plan about publishing more hubs and use an Excel schedule template for it. Well explained, voted up!

Sweet and simple. Good job, Simey, in explaining something that few would every think of doing.

Excel is about the only piece of software at which I am considered 'a whizz'! Most of it is self-taught but I cannot praise Excel highly enough, I struggle to find anything that it cannot do :o)

This is well written and explained and an extremely useful template.

Wonderful stuff! Do you have an article on how to make times add and subtract? For instance, if you have a beginning time of 2:00 p.m. and an end time of 3:15, can you make the spreadsheet figure out that the worktime is 1:15? And can you have accumulated times so that the total time spent on a project is provided (going beyond 24 hours)? If you don't have an article like this, maybe you can write one. : ))

6