Converting Spreadsheet to Calendar

[afiqul]

I have a spreadsheet set up as below where people have filled out preferences for shifts. However, I want to create individual google/iCal calendars for each person and so need to rearrange my data to have the dates along the top and the the different names as rows- with the table being filled in with whether they will be doing an morning, afternoon or evening shift- is this possible to do in excel, or do I have to do it manually?

Thanks in advance!


-----

Date	Mornings	Afternoons	Evenings
	Student 1	Student 2	Student 3	Student 4	Student 1	Student 2	Student 3	Student 4	Student 1	Student 2	Student 3
22/08/2017	Sh		ES		Sa
23/08/2017	Sh	RW	ES		Sa				Af	Ra	AR
29/08/2017	Sh	Sa	Ra		RW	ES			Af	AR
30/08/2017	RW	Sa	Ra		Sh	ES			Af	AR
05/09/2017	Sh	AR	Af		RW	Ra			Sa	ES
06/09/2017	RW	AR	Af		Sh	Ra			Sa	ES
12/09/2017	Sh	ES			Sa	AR	Af		RW	Ra
13/09/2017	Sh	ES			Sa	AR	Af		RW	Ra
19/09/2017		ES	Sa		RW	AR	Af		Sh	Ra
20/09/2017	RW	ES	Sa			AR	Af		Sh	Ra
26/09/2017	Sh	AR	Af	Ra	RW	ES
27/09/2017	RW	AR	Af	Ra	Sh	ES
03/10/2017	Sh	AR	Af		RW	ES
04/10/2017	RW	AR	Af		Sh	ES
10/10/2017	Sh	ES			RW	AR	Af
11/10/2017	RW				Sh	ES	AR	Af

<colgroup><col><col><col><col><col><col><col><col span="5"></colgroup><tbody>
</tbody>

 

[Perpa]

afiqul,
Welcome to the Forum.

You are showing 7 students in the example data you posted. This is important because you have to manually upload each student's requested shifts to their own Google Calendar. This can be time consuming. I have included some information below about how that is done. If you want to continue here are some hints:

Recommend you use ALL UPPER case letters for students initials

Recommend you put all students initials in dropdown, then copy the drop down to each cell to be selected…this will prevent misspelling and subsequent match finding easier.

Google Calendar will be looking for the calendar data in this format: The 'Subject' is the Students initials

Sheet1

	B	C	D	E	F
40	Subject	Start Date	Start Time	End Time	End Date
41	JA	9/30/2013	7:00 AM	7:00 PM	9/30/2013
42	KW	9/30/2013	7:00 PM	7:00 AM	10/1/2013
43	Intern	9/30/2013	2:00 PM	2:00 AM	10/1/2013

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 103px;"><col style="width: 103px;"><col style="width: 103px;"><col style="width: 103px;"><col style="width: 103px;"></colgroup><tbody>
</tbody>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4



But you must save this into CSV format before you upload to Google Calendar.
For each occurrence of a students initials you need the 'Start Date', 'Start Time', 'End Time', and 'End Date'
For your shifts, the 'Start' and 'End' dates will be the same Date
The Morning Shift will be something like 8:00 AM to 12:00 PM
The Afternoon Shift will be something like 12:00 PM to 4:00 PM
The Evening Shift will be something like 4:00 PM to 8:00 PM

Once those shift hours are set, we need to rearrange your data as per above format for each 'Student' ('Subject').
This can be accomplished with VBA fairly easily.
Then we need to save the signup list (for each Student) into CSV format (save as a CSV file).
This can be accomplished with VBA fairly easily as well.
Then each students CSV file can then be manually uploaded to each Student's Google Calendar.
So it is possible, but requires some VBA and then a manual upload of the CSV file for each student to their own Google Calendar.

Perpa

Here is more about Importing CSV files (calendar info from Excel) into Google Calendar
Import events to Google Calendar - Calendar Help

CSV stands for 'Comma Separated Values,' so CSV files have a comma between each item
of information they contain. Google Calendar supports most CSV formatted files from
commonly used calendar applications such as Yahoo! Calendar and Microsoft Outlook.
All CSV files must have a correctly formatted header. The minimum amount of header
information required to import events into Google Calendar is: Subject, Start Date.
With the header information above, Google Calendar will recognize the event as an All Day
event by default. To add more information to your events, simply add more headers.
Possible headers include: Subject, Start Date, Start Time, End Date, End Time, All Day Event,
Description, Location, and Private.
To edit a CSV file, first make sure that the file is originally saved with the .csv extension
(many text editing applications cannot "Save As..." a CSV, and manually appending the
extension does not make it a readable CSV file). An example CSV file looks like this:

Subject,Start Date,Start Time,End Date,End Time,All Day Event,Description,Location,Private
Final Exam,05/12/20,07:10:00 PM,05/12/07,10:00:00 PM,False,Two essay questions that will
cover topics covered throughout the semester,"Columbia, Schermerhorn 614",True
This event, 'Final Exam,' would then appear on May 12, 2020 from 7:10 PM to 10:00 PM. The
location would be 'Columbia, Schermerhorn 614,' the description would be 'Two essay
questions that will cover topics covered throughout the semester,' and it would be a private
event. Note that if any of your fields have commas in them (location, in the example above),
you can include them in your file by making sure to place quotation marks around the text.
Check out this article if you're having trouble importing your CSV file:
Fix problems importing - Calendar Help