Convert Data To ICS (Google Calendar)

[SoulfulNeo]

Hello,

I've spent quite a bit of time more than I should have going through a complex rota provided by my employer and extracting the necessary details to create a simple spreadsheet. The spreadsheet shows the week number, date, and the duty I'm on, along with the start and finish times. This format works well for me visually, but now I want to upload this data to Google Calendar, and I'm having trouble making it work.

I understand the basics: I need to set the right headers with the appropriate data underneath, and then save it as a .csv file for import into Google Calendar. However, I have a large amount of shift data spanning from now until the end of next year, so there must be an easier way to format this.

I've uploaded the entire worksheet, and I would appreciate guidance on how to convert this data into the right format, showing the "Duty" (e.g., Duty 1406), "Date" (with some shifts crossing over into the next day), and the "Start & Finish Time".

Any help would be greatly appreciated. Thank you in advance!

P.S. - I've successfully created the document that uploads events into Google Calendar (test.csv), but I'm looking for a quick and easy way to convert the data from my Excel document (4 Day Main Rota.xlsx) into the proper calendar format.

[JohnTopley]

It helps if the expected results data (Test.CSV) is aligned with the source data