Bi-weekly schedules in Excel

Voorbeeldbestanden bij dit artikel:
  • Bi-weekly schedules in Excel
  • Introduction

    All of us is using Excel for any kind of job, ranging from the simplest shopping list to the most elaborate financial models. I regularly see that Excel is used for work schedules, as it is so flexible. This article shows you a file with simple formulas for a bi-weekly schedule. For instance, your working hours could be set in a period of 2 weeks, rather than set every day the same way.

    Over 2 weeks time the work schema is fixed. Therefore it's rather useless to copy hours and dates everytime. We choose to set up a second table to hold the hours:

    Here we work with a set schema for 14 days: 2 weeks of 7 days. Hence the times in the table can easily be queried once we know the correct day index number (from 1 to 14).


    (Open the Excel file at the top of the page to play around with the formulas)
    In the second table "Number of hours" is calculated as (please note, I use Excel's table functionality, which makes the formulas readable):

    =[@[Ending hour]] - [@[Starting hour]]

    In the first table we have more formulas: The green columns are input for the user. You can pick any date you want.

    Day index (1 to 14): =WEEKDAY([@[Starting date]];2) + IF([@[Even week]]=1;7;0)

    Weeknumber: =WEEKNUM([@[Starting date]]-1)

    Even week (1 = yes, 0 = odd week): =--(ISEVEN([@Weeknumber]))

    #hours: =IF(LEN([@[Starting date]])=0;0;IF([@[Day off]]=1;0;INDEX(tbl_Planning[Number of hours];[@[Day index]])))

    Starting hour: =IF([@['#hours]]=0;0;INDEX(tbl_Planning[Starting hour];[@[Day index]]))

    Ending date (by assumption: equal to the Starting date): =[@[Starting date]]

    Ending hour: =IF([@['#uur]]=0;0;INDEX(tbl_Planning[Einduur];[@Dagindex]))

    Outlook synchronisation

    I use this file (complemented with VBA-code) to create Outlook appointments for every day. I only need to push a button to create and maintain all appointments in Outlook :-)

    Of course you can insert your own columns, like location or activity or manager mane, … decide whether you use table 1 or table 2: in the smallest table if the data are set for a period of 2 weeks, or in the bigger table if you need to be able to change the data on a day-by-day basis.


    Section contents

    About Wim

    Wim Gielis is a Business Intelligence consultant and Excel expert

    Other links