ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

Creating an Excel Schedule Template

Updated on July 13, 2012
The final Excel Schedule Template
The final Excel Schedule Template | Source

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.

Formatting the basic Excel schedule
Formatting the basic Excel schedule | Source

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

Adding the formulas to the basic schedule
Adding the formulas to the basic schedule | Source

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
Conditionally formatting today's date in row 1Conditionally formatting the weekends in the scheduleConditionally formatting today's date in the schedule sectionConditionally formatting the 'x' in the schedule
Conditionally formatting today's date in row 1
Conditionally formatting today's date in row 1 | Source
Conditionally formatting the weekends in the schedule
Conditionally formatting the weekends in the schedule | Source
Conditionally formatting today's date in the schedule section
Conditionally formatting today's date in the schedule section | Source
Conditionally formatting the 'x' in the schedule
Conditionally formatting the 'x' in the schedule | Source

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):

  • Area: Cells A1 to AC1 (apply this format to the top row only)
  • Use formula: =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)
  • Format: Use 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.

  • Area: Cells A1 to AC6
  • Use formula: =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)
  • Format: Use 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.

  • Area: Cells D1 to AB6
  • Use formula: =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 )
  • Format: Use 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.

  • Area: Cells D1 to AB6
  • Use formula: =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)
  • Format: Use 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.

Comments

    0 of 8192 characters used
    Post Comment

    • SimeyC profile imageAUTHOR

      Simon Cook 

      5 years ago from NJ, USA

      AMFredenburg: interesting idea - I'd probably have to expand it to show how to calculate difference in months, dates, years, hours etc......

    • AMFredenburg profile image

      Aldene Fredenburg 

      5 years ago from Southwestern New Hampshire

      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. : ))

    • Jools99 profile image

      Jools99 

      6 years ago from North-East UK

      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.

    • wilderness profile image

      Dan Harmon 

      6 years ago from Boise, Idaho

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

    • vox vocis profile image

      Jasmine 

      6 years ago

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

    • Natashalh profile image

      Natasha 

      6 years ago from Hawaii

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

    working

    This website uses cookies

    As a user in the EEA, your approval is needed on a few things. To provide a better website experience, hubpages.com uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

    For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: https://hubpages.com/privacy-policy#gdpr

    Show Details
    Necessary
    HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
    LoginThis is necessary to sign in to the HubPages Service.
    Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
    AkismetThis is used to detect comment spam. (Privacy Policy)
    HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
    HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
    Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
    CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
    Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
    Features
    Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
    Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
    Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
    Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
    Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
    VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
    PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
    Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
    MavenThis supports the Maven widget and search functionality. (Privacy Policy)
    Marketing
    Google AdSenseThis is an ad network. (Privacy Policy)
    Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
    Index ExchangeThis is an ad network. (Privacy Policy)
    SovrnThis is an ad network. (Privacy Policy)
    Facebook AdsThis is an ad network. (Privacy Policy)
    Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
    AppNexusThis is an ad network. (Privacy Policy)
    OpenxThis is an ad network. (Privacy Policy)
    Rubicon ProjectThis is an ad network. (Privacy Policy)
    TripleLiftThis is an ad network. (Privacy Policy)
    Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
    Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
    Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
    Statistics
    Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
    ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
    Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)