Jeffro, Sue, Diane,
I spent part of today experimenting. I think this could be a possible
/partial solution. Using this method a calendar could display a one-day
banner event showing the label "330 PD:1/28/08" or "PD:1/28/08" or however
the user wanted to configure it.
I wrote it up in a semi formal manner.
Tom Conrad
=== Displaying Julian dates in Outlook calendars. ===
=== Displaying Projected dates in Outlook calendars. ===
In recent posts to the Outlook calendar discussion group, regarding julian
dates, and project dates, it has been discussed that no method exists to
modify the Outlook standard calendar forms to accept user defined fields.
Many users would like to add Julian dates to their calendar printouts, and
one user wanted to add field that showed a projected date (start day +330
days).
The solutions previously posted recommended the creation of custom data
fields.
These fields are printable on table view calendar listings, but no one in
the group has proposed a method to get these fields to print on the standard
Outlook calendar views.
The method I describe below is a work-around. It isn' elegant but it is
pragmatic. These procedures can be used to create repetitive series for any
event. Such as: project dates, projected dates (Start date +330 days), etc.
The steps listed below will allow the user to use Excel to create a
spreadsheet containing a Julian date series.
The completed spreadsheet file must contain a single worksheet (sheet 1) and
the must be saved in CSV format, for later import into an outlook calendar.
If the user is slightly familiar with Excel these steps take about 20 minutes
or less to complete.
== Creating a Julian date spreadsheet ==
For a one-year julian calendar (non-leap year) the spreadsheet will contain
366 rows.
Row 1 will contain the labels.
Rows 2-366 will contain the necessary entries to create the (365) Julian
date calendar events.
The Julian date entries will import into an outlook calendar as all day
events. These events will display as banners on Outlook's day, week and
monthly calendar layouts. On table views these events will show a coded
subject line. The events are marked as free time for scheduling purposes.
Open Excel.
The completed file must contain only a single worksheet. Delete all other
worksheets.
Starting in cell A1 and proceeding through cell E1, enter following five
column labels (place one label into each cell A1 thru E1).
Subject
Start Date
End Date
Categories
Show time as
=== Start date column ===
In row 2 of the "Start date" column (cell B2)
- enter the first day of the year 1/1/2007
- Using the mouser or cursor:
Select cells b2 thru b366 (365 rows)
- On the edit menu, select Fill, series
On the series dialog box, Select the following factors-
series in: columns
type: date
date unit: day
step value" 1
stop value: is left blank.
Then select ok to fill the column with a date series.
=== End date column ===
Repeat the above steps for the "End date" column
or select, copy and paste cells B2:B366 into cells C2:C366.
=== Subject column ===
Enter the following formula into cell A2 of the subject column (A)
A2 =CONCATENATE("JD:",TEXT(($B2-VALUE($B$2)+1),"000"))
This formula will create labels in the subject filed of JD:001, JD:010, etc.
Copy the formula into cells cells A3:A366.
As the formula is copied into cells A3:A366 the formula references will
change to
A3 =CONCATENATE("JD:",TEXT(($B3-VALUE($B$2)+1),"000"))
AB4 =CONCATENATE("JD:",TEXT(($B4-VALUE($B$2)+1),"000"))
A366 =CONCATENATE("JD:",TEXT(($B366-VALUE($B$2)+1),"000"))
FOR the start date of 7/22/07, this formula yields a Subject field label of
JD:203
When the file is saved in CSV format the only the formula values will remain
in the cells.
Note- The projected day formula is:
A2 =CONCATENATE("PD:",TEXT((VALUE($B2+330)),"mm/dd/yy"))
A3 =CONCATENATE("PD:",TEXT((VALUE($B3+330)),"mm/dd/yy"))
A4 = etc.
For a start date of 7/22/07 This formula yields a Subject field label of
PD:06/16/08
=== Categories column ===
In the "Categories" column (D)
- enter "Julian Day" into cell D2
(enter projected day vice julian day if applicable)
- Select & copy cell D2 into cells D3
366.
=== Show time as column ===
In the "Show Time as" column (E)
- enter the number "3" into cell E2
- Select & copy cell E2 into cells E3:E366.
=== Saving the file in CSV format ==
On the file menu, select Save as
On the Save as dialog box
- Select a folder location to save the file or accept the default folder.
- enter the file name: Julian dates (or projected dates)
- Select the Save as Type: CSV (Comma delimited) (*.csv)
- Select the Save button to save the file.
Close the file and Exit Excel
=== Importing the CSV file into Outlook ===
Open an Outlook calendar.
- On the file menu, select import and export
- On the import and export wizard display
select import from another program or file and click next
- On the import file dialog
Select comma separated values (DOS) and click next
- Use Browse to locate the file Julian date.csv (or projected dates.csv)
Under the options section, select allow duplicates, click next
- Select the destination calendar folder, click next
On the final dialog box the display will show the following actions will be
performed by the import wizard
import Julian date.csv into folder <target calendar>
click on the finish button to complete the import.
A calendar view should now display daily events for all julian (project)
dates. I do recommend deleting the sat/sun events, as most business users may
not need these dates.
