How to Open an ICS File in Excel

Excel has no iCalendar importer. Renaming calendar.ics to calendar.csv and opening it produces a single column of BEGIN:VEVENT lines, not a table. You need to convert the format first. There are three practical routes.

Route 1: Convert to CSV First (Easiest)

1. Open the .ics in ICS Viewer and export as CSV.
2. In Excel, go to Data → From Text/CSV rather than double-clicking the file.
3. In the preview, set the start and end columns to Date explicitly, then Load.

Using the Data tab instead of double-clicking matters more than it sounds. Double-clicking lets Excel apply locale-based date guessing, which is where day/month swaps come from.

Route 2: Power Query (For a Repeating Feed)

If you re-export the same calendar every week, Power Query lets you build the transformation once and hit refresh afterwards. Load the .ics as a text file, then:

1. Split into rows on the line break character.
2. Filter to rows starting with SUMMARY:, DTSTART, or DTEND.
3. Add an index column, group every event block, and pivot the property names into columns.

This is fiddly to set up and it will not survive line folding — iCalendar wraps long lines by continuing them with a leading space, so a description containing a line break will split into fragments. Worth it only for a stable feed you process repeatedly; for one-off files, route 1 is faster and more reliable.

Route 3: Google Sheets, Then Download as .xlsx

If you want the calendar in a sheet without touching Excel's import dialog, import the CSV into Google Sheets (File → Import → Upload), set the locale under File → Settings so dates parse correctly, then File → Download → Microsoft Excel.

Calculating Hours Once It Is In

=(EndCell-StartCell)*24

Format as a number, not as time. To total only real meetings and exclude all-day blocks:

=SUMIFS(DurationRange, AllDayRange, "FALSE")

Then a PivotTable with the date as rows and duration as the sum gives you hours per day, per week, or per client in about thirty seconds.

What Excel Cannot Represent

A flat sheet loses three things from the original file, and it is worth knowing which:

• Recurrence rules. A weekly standup is one VEVENT with an RRULE in the source. Any sensible export expands it into individual rows — check that yours did, or your hour totals will be wildly low.
• Timezone identity. Times become plain local values with no zone attached.
• Attendee status. Accepted, declined, and tentative responses usually collapse into a single name list.