Bi-weekly schedules in Excel
- Jan. 2, 2016
 | Voorbeeldbestanden bij dit artikel: |
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.
