Copying a Google Calendar from 2025 to 2026, part 2

In a recent post (here), I looked at the first stage of exporting a Google Calendar as an .ics file, and the next stage; working out how to update the dates so that I can import the updated version back into Google Calendar.

In the previous post, I had my data in a text file, as a vertical list of text strings containing the dates which need to be updated (depending on the type of calendar item each relates to). There are three 'types' of data in the export, all at various lengths, with the key date information that I want slopping around nicely on the far right in the unbracketed entries below, but lurking somewhere in the middle on the two bracketed examples:

DTSTART;VALUE=DATE:20250210 (or DTSTART:20250210T070000Z or DTSTART;TZID=Pacific/Auckland:20250210T070000)

DTEND;VALUE=DATE:20250210 (or DTEND:20250210T080000Z or DTEND;TZID=Pacific/Auckland:20250210T080000)

Of course it couldn't be too simple, could it!

I needed to work out how to extract the date - 20250210 - from those various length strings, so then I could convert the date to a full date format (I wanted to see the formatted dates and days so I could work out which items needed to be moved to a particular day. This is for me to see which dates need to change to a particular day rather than a year less a day when moving items year to year: Zooms tend to be on a particular night of the week, so being able to see the date in date format with the day makes it much easier to make the right adjustment. To do that, ended up having to do a three step process (despite trying all sorts, I ended up giving up and doing it an easy way!). First, I extracted the data from the colon in each data string, using a 'mid' function in Excel in column I:

=MID(B30,FIND(":",B30)+1,LEN(B30)-FIND("T",B30))

Secondly, in column C, I ran the following:

=LEFT(I30,8)

Thirdly, I then picked up the product of that C30 in D30 with a date reorganise using Excel's 'left', 'mid', and 'right' functions, also with a custom date format with days ([$-en-NZ]dddd, d mmmm yyyy):

=DATE(LEFT(C30,4),MID(C30,5,2),RIGHT(C30,2))

Crikey. And that got me to being able to begin to update the calendar data.

Now I need to work out an easy way to bulk change dates...!

Sam