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

## Formulas

(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.

#### Homepage 