Create Calendar Events from an Excel Sheet with Python

Plenty of schedules live in spreadsheets: a training plan, a project's milestone list, an on-call rota, a list of contract renewal dates. People then retype the rows into their calendars, one by one, and miss the change made to row 14 last week. Calendar programs all import the same standard format, iCalendar (.ics), so a script can turn the sheet into a file that Outlook, Google Calendar and Apple Calendar load in one go — and, done properly, re-import later to apply changes rather than adding duplicates. This page shows the conversion with pandas and the icalendar package, including the details that trip up hand-written .ics files: all-day end dates, time zones and stable identifiers. It belongs to Handling Excel File Formats and Conversions.

Prerequisites

pip install pandas openpyxl icalendar

The examples use icalendar 7.3. schedule.xlsx has one row per event with an ID, title, date, start and end times, location, owner and notes. Two rows have no times — they are all-day events — and the last row is an empty leftover that must be skipped.

Step 1 — Read the sheet and build start and end timestamps

Excel stores dates and times separately when they are typed into separate columns, and openpyxl returns them as datetime and time objects. Combine them into one start and one end per row:

import datetime as dt
import pandas as pd

df = pd.read_excel("schedule.xlsx", sheet_name="Schedule")
df = df.dropna(subset=["Event", "Date"]).fillna({"Location": "", "Notes": ""})
df["Date"] = pd.to_datetime(df["Date"]).dt.date
df["AllDay"] = df["Start"].isna()

def combine(day, t):
    return None if pd.isna(t) else dt.datetime.combine(day, t)

df["StartAt"] = [combine(d, t) for d, t in zip(df["Date"], df["Start"])]
df["EndAt"] = [combine(d, t) for d, t in zip(df["Date"], df["End"])]
print(df[["ID", "Event", "AllDay", "StartAt", "EndAt"]].to_string(index=False))

Dropping rows without an event name or date removes the leftover row. The time columns come back as Python time objects because the cells hold time values; if your sheet stores times as text such as "09:30", parse them first with pd.to_datetime(df["Start"], format="%H:%M").dt.time. Rows without a start time are flagged as all-day.

Step 2 — Attach the time zone

A timestamp without a zone is ambiguous: "09:30" means one thing in London and another for a colleague in New York who imports the same file. Attach the zone the schedule was written in, and let each calendar app convert it for its user:

from zoneinfo import ZoneInfo

TZ = ZoneInfo("Europe/London")
df["StartAt"] = [s.replace(tzinfo=TZ) if pd.notna(s) else None for s in df["StartAt"]]
df["EndAt"] = [e.replace(tzinfo=TZ) if pd.notna(e) else None for e in df["EndAt"]]
print(df.loc[df["ID"] == "MS-02", "StartAt"].iloc[0].isoformat())

The budget workshop on 3 November starts at 14:00+00:00 (GMT), while the October sessions are at +01:00 (BST): the zone database knows that the clocks change on 25 October 2026, so a single zone name gives the right offset on both sides of the switch. Hard-coding an offset such as +01:00 instead would put every November event an hour out.

Step 3 — Write the events

import hashlib
from icalendar import Calendar, Event

def stable_uid(row_id):
    return hashlib.sha1(f"schedule.xlsx/{row_id}".encode()).hexdigest()[:16] + "@reports.example.com"

cal = Calendar()
cal.add("prodid", "-//Example Reports//Schedule export//EN")
cal.add("version", "2.0")
stamp = dt.datetime.now(dt.timezone.utc)
for row in df.itertuples(index=False):
    ev = Event()
    ev.add("uid", stable_uid(row.ID))
    ev.add("dtstamp", stamp)
    ev.add("summary", row.Event)
    if row.AllDay:
        days = 2 if row.ID == "HD-01" else 1
        ev.add("dtstart", row.Date)
        ev.add("dtend", row.Date + dt.timedelta(days=days))
    else:
        ev.add("dtstart", row.StartAt)
        ev.add("dtend", row.EndAt)
    if row.Location:
        ev.add("location", row.Location)
    if row.Notes:
        ev.add("description", row.Notes)
    cal.add_component(ev)
cal.add_missing_timezones()
with open("schedule.ics", "wb") as fh:
    fh.write(cal.to_ical())
print(cal.to_ical().decode().count("BEGIN:VEVENT"), "events written")

Three details in this loop decide whether the file imports cleanly:

• All-day end dates are exclusive. An all-day event on 30 October has DTSTART;VALUE=DATE:20261030 and DTEND;VALUE=DATE:20261031. Setting the end to the same day makes some apps show nothing at all. The two-day office closure ends on the 26th to cover the 24th and 25th; in a real sheet, take the number of days from an end-date column.
• Stable UIDs. The UID is derived from the row's ID, not generated randomly, so exporting again after an edit produces the same identifier. Calendar apps treat an event with a known UID as an update to the existing entry rather than a new one.
• Time zone definitions. add_missing_timezones() adds a VTIMEZONE block for Europe/London, which the standard requires whenever an event uses TZID. Without it Google Calendar usually guesses correctly and stricter clients may not.

Step 4 — Read the file back to check it

Parsing the output with the same library catches mistakes before anyone imports it:

from pathlib import Path

check = Calendar.from_ical(Path("schedule.ics").read_bytes())
for ev in check.walk("VEVENT"):
    start = ev.decoded("dtstart")
    kind = "all day" if not isinstance(start, dt.datetime) else start.strftime("%H:%M %Z")
    print(f"{str(ev['summary']):35} {start:%d %b} {kind:10} {str(ev.get('description', ''))[:30]}")

Every event comes back with its original text — Bring a laptop; Python 3.12+ included, even though the file stores it as Bring a laptop\; Python 3.12+. The library escapes commas, semicolons and backslashes and folds long lines at 75 octets, the two rules hand-written .ics files most often break. Writing the file with f-strings is tempting; it works until the first note with a comma in it.

Step 5 — One file per person

Sending each owner only their own events is a group-by away:

out = Path("calendars")
out.mkdir(exist_ok=True)
for owner, group in df.groupby("Owner"):
    personal = Calendar()
    personal.add("prodid", "-//Example Reports//Schedule export//EN")
    personal.add("version", "2.0")
    for ev in check.walk("VEVENT"):
        if ev["uid"] in {stable_uid(i) for i in group["ID"]}:
            personal.add_component(ev)
    personal.add_missing_timezones()
    (out / f"{owner.lower()}.ics").write_bytes(personal.to_ical())
print(sorted(p.name for p in out.iterdir()))

Each file can be attached to an email — see Send Each Recipient Their Own Excel Report for the mailing loop. Use the MIME type text/calendar for the attachment so mail clients offer to add the events.

Step 6 — Add a reminder

People import calendar files so that their calendar reminds them. A VALARM component inside each event asks the app to show a notification a set time before the start:

from icalendar import Alarm

for ev in check.walk("VEVENT"):
    if isinstance(ev.decoded("dtstart"), dt.datetime):
        alarm = Alarm()
        alarm.add("action", "DISPLAY")
        alarm.add("description", f"Reminder: {ev['summary']}")
        alarm.add("trigger", dt.timedelta(minutes=-30))
        ev.add_component(alarm)
Path("schedule_reminders.ics").write_bytes(check.to_ical())
print(check.to_ical().decode().count("BEGIN:VALARM"), "reminders added")

The negative trigger means "30 minutes before the start". All-day events are skipped here because a reminder relative to midnight is rarely useful; give them a trigger such as -P1D (one day before) instead if your audience wants one. Calendar apps are free to ignore alarms in imported files, and some apply the user's own default notifications instead, so treat the reminder as a suggestion rather than a guarantee.

Common pitfalls

• Same-day end for all-day events. DTEND is exclusive; add a day.
• Random UIDs. A new UID on each export creates duplicates on re-import; derive it from a stable row key.
• Fixed UTC offsets. Use a zone name so daylight-saving changes are handled.
• Hand-written files. Unescaped commas and unfolded long lines break strict parsers; let icalendar write the text.
• Subscriptions versus imports. An imported .ics is a one-off copy. For a calendar that refreshes itself, publish the file at a stable URL and subscribe to it — see Publishing Excel Reports to Cloud Storage.

Performance notes

Building events is cheap: a few thousand rows convert in well under a second, and the time zone definitions are added once per zone, not per event. The practical limits are on the receiving side. Importing a file with thousands of events into a personal calendar is slow and hard to undo, so for large schedules publish per-person or per-team files, and keep past events out of the export unless someone needs the history.

Conclusion

Read the schedule with pandas, combine date and time columns into timestamps, attach a named time zone, and write each row as an iCalendar event with a UID derived from the row's ID. Give all-day events an exclusive end date, let add_missing_timezones() add the zone definitions, parse the file back as a check, and split it per owner when each person should only see their own entries.

Frequently asked questions

How do I convert Excel rows into calendar events? Read the sheet with pandas, build start and end datetimes for each row, and write them as VEVENT components with the icalendar package. Save the result as an .ics file, which Outlook, Google Calendar and Apple Calendar can import.

How do I create all-day events in an .ics file? Use date values instead of datetimes for DTSTART and DTEND, and set DTEND to the day after the last day, because the end date is exclusive.

Why does re-importing my .ics file create duplicate events? Each export generated new UIDs. Give every event a UID derived from a stable key, such as the row's ID, so calendar apps recognise the event and update it.

How do I set the time zone for events exported from Excel? Attach a named zone with zoneinfo, for example ZoneInfo("Europe/London"), to each start and end time, and call add_missing_timezones() on the calendar so the file includes the matching VTIMEZONE definition.

Related

• Up one level: Handling Excel File Formats and Conversions — converting sheets to other formats.
• Build a Calendar Sheet in Excel with Python — a printable month grid instead of calendar events.
• Handle Timezones in Excel Timestamps with Python — zone conversion in depth.
• Convert Excel Time Values and Durations — when time columns arrive as fractions or text.