My racing calendar for next year contains around 150 events.
I thought it would be easier to have ICS files for most of these events so I
could upload them to my diary rather than type them all in. There are a
number of web sites that say they can do this for you, just enter your event
details. Two problems! The first was that some of the sites assume
your dates are in USA format, MM/DD/YY, and that gave, ah, surprising results in
my diary. The second was that some sites cheerfully accepted my data
(enter your data for free, they said), and then forwarded me to a page where I
could pay to download the ICS file. Hmmmm.
My suggestion is to play with the spreadsheet by constructing
an ICS calendar file with just one event and see how that looks when uploaded
into your diary. Delete the event and try another parameter setting until
you are happy. Only then construct the calendars you want. I
eventually constructed and uploaded 7 calendars for 2026 to suit my requirements
-- 6M at home with the Broads RYC, 6M at home with the Norwich MYC, 6M Open
away, similar three for the IOM, and one for the 36" Open away.
These are the instructions as found in the first page of the
workbook.
|
Constructing an ICS Calendar file from a list of
events in Excel. |
|
Overview:
Set the parameters.
List the event dates (and their names and
venues if desired).
Copy the ICS code lines into a Word file.
Save the file as *.TXT and then rename it
to *.ICS.
Open the ICS file in the diary application. |
|
|
|
PRELIMINARIES |
|
It
is assumed that there is already a club
calendar, probably in Excel, maybe in Word.
In order to use it in this Workbook, the
calendar data needs to be arranged so each event
is in its own row with its date at the least. |
|
The date must be in the
format DD/MM/YYYY.
The event may also have a name and a
venue, which can be any text as may be required. |
|
For the diary in Style 1,
the event's name and venue are the second and
third cells in its row.
For Style 2, just its name.
For Style 3, no further data is needed.
(See Style descriptions below.) |
|
|
|
SET PARAMETERS in the Events List sheet |
|
ONLY enter parameters
into the yellow (and pink) cells.
Other cells contain formulae or reserved
codes and must remain intact. |
|
Set the notional year for
the calendar, "2026" here, and the start and end
times of events, "10:00" and "12:30" here. |
|
NB
At data entry it is essential that the
times use the 24-hour clock in the specific
format "HH:MM" -- that is, there MUST be a colon
":" separating hours from minutes. |
|
If
events have different start and end times,
apologies but this Workbook doesn't do that.
The ICS file can be edited later in Word
or Notepad or similar to change the times, see
below. |
|
Set the club acronym (or
single word;
that is, no spaces), "BRYC" here, and the
default venue, "Filby" here. |
|
Set the boat class, "6M"
here (acronym or single word).
It is used as described below.
If there is no particular class for the
calendar, provide some text instead, such as
"Boat" or "Sailing" or "All". |
|
Set the Style for the
resulting diary entries, 1, 2, or 3.
(See below.) |
|
The ICS date stamp,
DTSTAMP, is the nominal "as of" date of the
diary entries, set to 1/1/2025 here.
The VERSION and PRODID are also ICS
nominal values.
No need to change any of these. |
|
Note, however, that the UID
is not a nominal value if there are or will be
multiple calendars.
For each distinct calendar created using
this Workbook, the UID must be a unique value. |
|
The
Workbook assumes that there is a separate club
calendar for each of the club's Classes, such
that members would use one or more ICS calendar
files, one each for the class(es) that interest
them. |
|
In
addition, a member might belong to more than one
club and might wish to load more than one Class
ICS file, one from each club.
For this reason the Club acronym is
encoded into the UID. |
|
The
Workbook suggests a UID of "YYClubClass".
YY is taken from the notional year, "26"
here;
Club is the acronym, "BRYC" here;
and Class is "6M" here.
Change the UID to any other value if
required, but NO spaces! |
|
Each
event is given a serial number, starting from
pink cell "0" here, then incrementing by 1,
which is appended to the UID to make a unique
reference number in the calendar for each event. |
|
Change the pink cell "0" to an approriate serial
number if there is ever a further ICS file of
events to complement a previous "YYClubClass"
ICS calendar. |
|
If
UID or pink cell is not changed to a new
(unique) value, the further file is unlikely to
be processed by the diary or calendar app. |
|
An example event date is
shown boxed, along with its resulting ICS event
code line in dark green.
It may be helpful to play with the
parameters and Styles to see their effects
before proceeding. |
|
Note how
the contents of the ICS event code line seem
packed together.
This is deceptive. |
|
Each ICS
code element is in fact separated by a CHAR(10)
or <LF> in old
money.
The required <CR> appears when the cells
are pasted into a Word document (NOT Notepad or
other text editor). |
|
|
|
ENTER EVENT DATA in the Events List sheet |
|
The event data from the
donor sheet is entered row by row, starting
below
the row of the boxed example event date and its
example cells. |
|
Style 1 -- Copy and
Paste
Values
of the
dates,
names, and venues
of the event rows from the donor sheet to the
Events List sheet.
This Style is useful if there are a
variety of venues and if the detail of the event
names is desired. |
|
Note
that the
Paste
Value
overwrites the Workbook formulas in the name and
venue columns. |
|
Style 2 -- Copy and
Paste
Values
of the
dates
and names
of the event rows from the donor sheet to the
Events List sheet.
The Workbook will set the venue to the
Default Venue.
This Style useful if the detail of the
event names is desired. |
|
Note
that the
Paste
Values
overwrites the Workbook formula in the name
column. |
|
If
there is no parameter for Default Venue (it is
blank), the venue will display as ".", no
LOCATION will be set in the ICS event code line,
and no hence venue will appear in the diary. |
|
However, after data entry, specific text can be
entered to overwrite the "." of an event's
venue, eg "Norwich".
The Workbook will then set LOCATION to
the specific text, "Norwich". |
|
Style 3 -- Copy and
Paste
Values
of the
dates
of the event rows from the donor sheet to the
Events List sheet.
The Workbook will set the name to "Class
+ Default Venue", such as "6M Filby".
Useful for short generic event names in
the diary. |
|
If
there is no Default Venue, the event name will
be simply "Class", eg "6M".
No LOCATION will be set in the ICS event
code line, and hence no venue will appear in the
diary. |
|
However, after data entry, specific text can be
entered to overwrite the venue ".", eg
"Norwich".
The Workbook will then set name to "Class
+ specific text", eg "6M Norwich", and set
LOCATION to the specific text, eg "Norwich". |
|
Then
copy the relevant light green remaining cells
(formulas) from the example row (columns E
onwards for Style 1, columns D onwards for Style
2, columns C onwards for Style 3) and paste them
into all the rows which have events. |
|
NB
Paste using "ordinary" Paste,
not
any of the varieties of "Paste Special…", so
that the formulas are correctly copied. |
|
|
|
COPY TO WORD the ICS code lines from the Events
List sheet |
|
Open a blank Word document,
and into it:
(1) copy and paste the
light blue
ICS Calendar start code line.
Note how the ICS elements transform into
separate lines in the Word document. |
|
NB
Paste into the Word document using
"ordinary" Paste,
not
any of the varieties of "Paste Special…", so
that the hidden control characters transfer
correctly. |
|
Then:
(2) copy and paste the
dark green
ICS event code lines.
Exclude
the example event.
Note again how the ICS event code line
elements transform into separate lines in the
Word document. |
|
Finally:
(3) copy and paste the
dark blue
ICS Calendar end code line. |
|
Save As
a Plain Text (.TXT) file.
Chose the Windows (Default) TXT file
format.
Ignore the "preview" offered.
Close Word. |
|
|
|
RENAME TO ICS |
|
In File Explorer or
similar, rename the file extension from .TXT to
.ICS.
Ignore the warning about changing file
extensions. |
|
The ICS file
can
now be edited by Notepad or similar, if desired.
For example, find "DTEND:20260401T123000"
and change it to "DTEND:20260401T160000" to end
at 16:00. |
