Sign up for the ITPro Today newsletter
Stay on top of the IT universe with commentary, news analysis, how-to's, and tips delivered to your inbox daily.
Outlook: Printing a Timeline View of Outlook Data
Use Excel to generate a printable Gantt chart from Outlook timeline data.
Sue Mosher
June 24, 2002
4 Min Read
I created a custom view by using the timeline style, but I can't print it. The Print button is disabled in that view. Can Outlook print timeline views?
As far as I know, Outlook can't print a timeline view—probably because Outlook doesn't know how to arrange the view on paper. The Microsoft Knowledge Base suggests that a workaround might be to export the data to Microsoft Excel, then use the .xls file to create a Gantt chart from the timeline data. The Microsoft article "XL: How to Create a Gantt Chart Using Hours as the Scale" (http://support.microsoft.com/default.aspx?scid=kb;en-us;q152820) provides some basic pointers but doesn't tell you how to get the Outlook data into the correct format.
An Excel chart can easily display data for only a limited number of days, so you might start with 1 or 2 weeks' worth of data. To test this technique, I exported data from my Calendar folder to Excel and used the Map Custom Fields button to export only the Subject, StartDate, StartTime, EndDate, and EndTime fields. After opening the worksheet in Excel, I deleted all but the data from 3/16/2002 to 3/31/2002, leaving 17 calendar entries to work with.
To create the Gantt chart, you need three columns of data. You'll need to calculate the start date and time and the duration of each appointment. The descriptive label will be the Subject from your appointments.
Figure 1 shows my test worksheet. Outlook's Export routine automatically put the field names in cells A1—E1; I added the column headings in cells F1—H1. Because Excel treats column headings that are contiguous to data cells as range names for those cells, you can easily construct formulas with just the field names. In cells F2—H2, put the appropriate formula from those that Table 1 lists. Then, copy the formula from cells F2—H2 down the rows until you've applied those formulas to all exported calendar data. The calculations in columns F—H result in serial dates (i.e., numbers that contain both date and time information). For example, adding the values that Excel displays as 3/29/2002 and 7:00:00 p.m. yields the serial date 37344.79. The date 24 hours later would be 37345.79, an increase of exactly 1.
Choose Tools, Sort and sort the data in the Start column in ascending order. Now, you're ready to make the chart. The process is a bit complicated, so be sure you do each step in order.
Choose Insert, Chart to launch the Chart Wizard.
From the Chart type list, choose Bar, and for Chart sub-type, choose Stacked Bar (the second choice in the first row). Click Next.
On the Data Range tab, clear the text in the Data range box, then click the Series tab.
In the Series list (which should contain no entries—if the list contains entries, click Remove to remove every series), click Add. Click in the Name box, then click cell F1 on the worksheet. Click in the Values box, and clear any text there. Starting with cell F2, select all remaining data in column F (the Start column) to add the Start data as the first series.
Click Add again and add the duration as the second series. (Click in the Name box, then click cell H1 on the worksheet. Click in the Values box, clear any existing text, then select all remaining data in column H starting with cell H2.)
Click in the Category (X) axis labels box, then select all the data in column A (the Subject column), starting with row A2. Click Next.
Click Next again to skip the Chart Options page (you can set any desired options later). On the Chart Location page, select As new sheet, change the chart's name if you'd like, and click Finish.
The bars for Start and Duration will appear in different colors. To make the Start data function purely as a positioning element, you need to make the Start bars invisible. Click any Start data bar to select the series of start values, then choose Format, Selected Data Series. Under both Border and Area, choose None; click OK.
Next, adjust the X-axis so that it begins with the first date from your exported Calendar data. Refer to the first item in the Start column, and note the integer part. For example, the first date in my test is March 16, 2002, which is serial date 37331. On the chart, select the X-axis, then choose Format, Selected Axis. On the Scale tab, set the Minimum value to the integer value from the first item in the Start column. Do the same with the Category (X) axis crosses at value. On the Number tab, under Category, choose Date and select a date format from the Type list. Click OK to save the changes.
Click the legend box on the right side of the chart, then press Delete to remove the box.
The chart should now resemble Figure 2, which shows a small bar to represent each appointment's duration.
About the Author
You May Also Like
.jpg?width=700&auto=webp&quality=80&disable=upscale)
