Book Image

The Microsoft Outlook Ideas Book

By : Barbara March
Book Image

The Microsoft Outlook Ideas Book

By: Barbara March

Overview of this book

Microsoft Outlook, in tandem with Microsoft Exchange Server, provides a powerful environment for sharing information. This book will show you how to take advantage of that to construct solutions for your business or organization from the features of Outlook. This book is a collection of scenarios that incorporate and link many Outlook components to produce surprisingly powerful functionality. Without the need for code or specially-written applications, you will be extracting information from your Outlook Calendar, Contacts and Tasks folders to create solutions like these: Monitoring staff leave and printing schedules Managing meeting rooms and printing invoices Managing fleet vehicles, their records, and servicing Managing a school class calendar, student records, attendance, assignments, and reports
Table of Contents (8 chapters)

A Staff Leave Calendar


In many offices, the details of staff leave and sick leave are recorded and maintained in Excel spreadsheets or some other third-party program. It is also likely that annual leave and sick leave are also recorded as one-day events in the grey area of someone’s Outlook calendar. Duplicating data in this manner takes valuable time and resources and risks error.

Depending on the level of analysis required of your leave and sick leave data, you may find that Outlook can do everything that is necessary by utilizing the following ideas, which will require entering the data only once.

If you monitor the leave and sick leave for a small number of people, you can manage staff leave in your main calendar. Each day of leave would be a one-day event and would appear in the grey area at the top of the calendar, along with any other one-day events you may have. However, if you have a large number of leave events, you can easily lose other one-day events and too many one-day events at the top of the calendar can also severely restrict your view of the rest of your calendar, as illustrated in the following screenshot:

If you prefer to keep a separate calendar just for leave events, you will need to create a new calendar named, for example, Staff Leave Calendar.

The new view that we shall be creating can be for either your main calendar or a separate calendar specifically for leave. The new view will have new fields that will calculate the amount of leave taken and the number of days remaining from the entitlement and from this view we shall be able to produce leave schedules by staff member and by Department.

In preparation for creating the new view, we need to do to following:

  1. 1. Create the following categories:

CATEGORY NAME

PURPOSE

A/L

Annual leave category

S/L

Sick leave category

AF A/L

(meaning: Andrew Fuller annual leave)

Annual leave category for each member of staff

AF S/L

(meaning: Andrew Fuller sick leave)

Sick leave category for each member of staff

Admin

 

Orders

 

Despatch

 

etc.

 
  1. 2. As staff sick leave is reported or you are notified of staff annual leave dates, create leave events in the calendar, either as separate one-day events or over several days, but only enter the leave day events into calendar days that count as leave days i.e. working weekdays.

  2. 3. In the subject of the events, state the name or initials of the staff member and a notation to indicate whether it is annual leave or sick leave.

  3. 4. In the Location field, enter the Department where the individual works for e.g., Despatch.

  4. 5. Assign the three appropriate categories to the leave events e.g. A/L, AF A/L, Despatch. The categories in this example indicate that the event is annual leave, specifically AF’s annual leave and that AF is located in the Despatch Department.

  5. 6. Ensure that there is a check in all day event and Show time as free in the calendar items.

  6. 7. If you wish to have an automatic reminder of the leave events, you should be aware that Outlook reminders fire only on events in the default calendar and not on events in a calendar that you have created yourself. This may be a consideration when deciding whether to use your main calendar or create a separate leave calendar.

  7. 8. Link the leave events in the calendar to the relevant staff contact by clicking on the Contacts button at the foot of the event window and selecting the member of staff from the Contacts folder. Ensure that you have linked the staff Contacts folder to the Staff Leave Calendar folder from the Contacts folder Properties | Activities tab and the leave events will automatically be recorded on the Activities tab of the staff Contacts files.

  8. 8. Link the leave events in the calendar to the relevant staff contact by clicking on the Contacts button at the foot of the event window and selecting the member of staff from the Contacts folder. Ensure that you have linked the staff Contacts folder to the Staff Leave Calendar folder from the Contacts folder Properties | Activities tab and the leave events will automatically be recorded on the Activities tab of the staff Contacts files.

Creating the Calendar View

We will now create the view called Staff Annual Leave, either as a view of the main calendar or of a specific Staff Leave Calendar as follows:

  1. 1. Create a new view from the Define Views | New option and name the view Staff Annual Leave.

  2. 2. In the View Summary dialog box, click Fields, select the fields: Subject, Location, Start, and Duration, and click OK.

  3. 3. We will now create a New Field called Entitlement:

Option

Value

Name

Entitlement

Type

Number

Format

Raw

  1. 4. We will also create the following two Formula fields:

Name

In the Formula Field window

Last Day

Format ([End]- 1,"ddd dd mm yy)"

Days Remaining

([Entitlement]-([Duration]/1440)) & " Days"

  1. 5. At this point, let’s check the fields in our view and rearrange them into the order shown in the following screenshot. Click OK to return to the View Summary.

  1. 6. Continuing the design of the view, set Group By to Categories, with no check in Show field in view.

  2. 7. Set Sort items by to Start | Ascending, then by Subject | Ascending. If Outlook asks if you wish to show the End field in the view, click the No button.

  3. 8. From the Filter | More Choices | Categories option, select all the A/L categories for staff members.

  1. 9. To restrict the records in the view to those in the current year, add a filtering criterion in the Filter | Advanced window with the following settings and then click Add to List.

Option

Value

Field

Start

Condition

Between

Value

1/1/05 and 31/12/05

  1. 10. To make the final view of the leave records easier to read, we can apply the following font settings through the Automatic Formatting option that will show leave already taken as struck out and future leave as bold.

Formatting Rule Name

Field

Condition

Value

Font Format

Future

Start

on or after

today

Bold

Past

Start

on or before

today

Strikeout

  1. 11. Exit the View Summary and from the Format Columns dialog box rename the following fields as follows:

Field name

New name

Subject

Staff Leave

Location

Department

Start

First Day

What Just Happened?

We have just created a view that can be applied to either a default calendar or a separate leave calendar that will hold the annual leave records of the members of staff in our organization and will make this detailed information available instantly and simply by switching from the Day/Week/Month view to the Staff Annual Leave view.

  • To calculate the value of the Last Day field, we did not use the Outlook field End to show the last day of the event because an Outlook event is for one full day, 24 hours, from midnight to midnight. So the End field will actually show an event ending on the following day. Our new field, Last Day, will subtract one day from the end date and thereby reflect the true span of the event.

  • The Format ([End- 1]) function around the calculation will format the result according to the formatting template "ddd dd mm yy" which shows the day and date without the time.

  • The formula that calculates the value of the Days Remaining field divides the Duration field by 1440 before subtracting it from the Entitlement field. This is because, although the Duration field displays the length of time in days, it actually holds the time span as multiples of 24 hours in minutes. It is necessary therefore to divide Duration by the number of minutes in 24 hours, which is 1440.

To enable the calculation of the total amount of leave taken and the balance available, you will need to enter manually the number of leave entitlement days for the year into the first Entitlement field of the new leave year. Every member of staff starts the new leave year with a leave entitlement of a certain number of days. In the example overleaf, CW starts the new leave year with 25 days.

Outlook will subtract from the entitlement figure the number of days taken in the Duration field and return the balance in the Days Remaining field. As more leave events are created in the calendar and to produce a running sum, bring down the figure from the previous Days Remaining field into the next Entitlement field.

The Result

Here is the finished view of the calendar:

This view of the calendar can be printed, either the whole table, which would produce a schedule of leave for all staff, or only selected rows for individuals. If you wish to print individual schedules, select only the rows for the individual staff member in the view and from the File menu choose Print, or click the Print button. Click Page Setup to set the print style and to type a Header and Footer, and Preview to see the result.

Looking at CW’s leave record, we can see the results of the Automatic Formatting that we set up; past leave has been struck out and future leave is in bold.

We can also see from CW’s records that on 28th November 2005 he will be taking only half a day as leave. Outlook can handle a half-day in its calculations but how was it recorded in the calendar? As has been mentioned before, in Outlook, a day is 24 hours, so to record CW’s half day we have to block out 12 hours, from midday to midnight or midnight to midday that day (this would be another good reason to use a separate calendar for leave).

As we have linked the Leave events to the staff contacts’ records, the Leave items will appear on the Activities tab of the staff members’ records as illustrated opposite: