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!


-----


[TABLE="width: 1584"]
<colgroup><col><col><col><col><col><col><col><col span="5"></colgroup><tbody>[TR]
[TD]
Date[/TD]
[TD="colspan: 4"]Mornings [/TD]
[TD="colspan: 4"]Afternoons[/TD]
[TD="colspan: 3"]Evenings[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Student 1[/TD]
[TD]Student 2[/TD]
[TD]Student 3[/TD]
[TD]Student 4[/TD]
[TD]Student 1[/TD]
[TD]Student 2[/TD]
[TD]Student 3[/TD]
[TD]Student 4[/TD]
[TD]Student 1[/TD]
[TD]Student 2[/TD]
[TD]Student 3[/TD]
[/TR]
[TR]
[TD]22/08/2017[/TD]
[TD]Sh[/TD]
[TD][/TD]
[TD]ES[/TD]
[TD][/TD]
[TD]Sa[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]23/08/2017[/TD]
[TD]Sh[/TD]
[TD]RW[/TD]
[TD]ES[/TD]
[TD][/TD]
[TD]Sa[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Af[/TD]
[TD]Ra[/TD]
[TD]AR[/TD]
[/TR]
[TR]
[TD]29/08/2017[/TD]
[TD]Sh[/TD]
[TD]Sa[/TD]
[TD]Ra[/TD]
[TD][/TD]
[TD]RW[/TD]
[TD]ES[/TD]
[TD][/TD]
[TD][/TD]
[TD]Af[/TD]
[TD]AR[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]30/08/2017[/TD]
[TD]RW[/TD]
[TD]Sa[/TD]
[TD]Ra[/TD]
[TD][/TD]
[TD]Sh[/TD]
[TD]ES[/TD]
[TD][/TD]
[TD][/TD]
[TD]Af[/TD]
[TD]AR[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]05/09/2017[/TD]
[TD]Sh[/TD]
[TD]AR[/TD]
[TD]Af[/TD]
[TD][/TD]
[TD]RW[/TD]
[TD]Ra[/TD]
[TD][/TD]
[TD][/TD]
[TD]Sa[/TD]
[TD]ES[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]06/09/2017[/TD]
[TD]RW[/TD]
[TD]AR[/TD]
[TD]Af[/TD]
[TD][/TD]
[TD]Sh[/TD]
[TD]Ra[/TD]
[TD][/TD]
[TD][/TD]
[TD]Sa[/TD]
[TD]ES[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12/09/2017[/TD]
[TD]Sh[/TD]
[TD]ES[/TD]
[TD][/TD]
[TD][/TD]
[TD]Sa[/TD]
[TD]AR[/TD]
[TD]Af[/TD]
[TD][/TD]
[TD]RW[/TD]
[TD]Ra[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13/09/2017[/TD]
[TD]Sh[/TD]
[TD]ES[/TD]
[TD][/TD]
[TD][/TD]
[TD]Sa[/TD]
[TD]AR[/TD]
[TD]Af[/TD]
[TD][/TD]
[TD]RW[/TD]
[TD]Ra[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19/09/2017[/TD]
[TD][/TD]
[TD]ES[/TD]
[TD]Sa[/TD]
[TD][/TD]
[TD]RW[/TD]
[TD]AR[/TD]
[TD]Af[/TD]
[TD][/TD]
[TD]Sh[/TD]
[TD]Ra[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20/09/2017[/TD]
[TD]RW[/TD]
[TD]ES[/TD]
[TD]Sa[/TD]
[TD][/TD]
[TD][/TD]
[TD]AR[/TD]
[TD]Af[/TD]
[TD][/TD]
[TD]Sh[/TD]
[TD]Ra[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]26/09/2017[/TD]
[TD]Sh[/TD]
[TD]AR[/TD]
[TD]Af[/TD]
[TD]Ra[/TD]
[TD]RW[/TD]
[TD]ES[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]27/09/2017[/TD]
[TD]RW[/TD]
[TD]AR[/TD]
[TD]Af[/TD]
[TD]Ra[/TD]
[TD]Sh[/TD]
[TD]ES[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]03/10/2017[/TD]
[TD]Sh[/TD]
[TD]AR[/TD]
[TD]Af[/TD]
[TD][/TD]
[TD]RW[/TD]
[TD]ES[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]04/10/2017[/TD]
[TD]RW[/TD]
[TD]AR[/TD]
[TD]Af[/TD]
[TD][/TD]
[TD]Sh[/TD]
[TD]ES[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/10/2017[/TD]
[TD]Sh[/TD]
[TD]ES[/TD]
[TD][/TD]
[TD][/TD]
[TD]RW[/TD]
[TD]AR[/TD]
[TD]Af[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11/10/2017[/TD]
[TD]RW[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Sh[/TD]
[TD]ES[/TD]
[TD]AR[/TD]
[TD]Af[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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
Subject	Start Date	Start Time	End Time	End Date

<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>
[TD="bgcolor: #CACACA, align: center"]40[/TD]

[TD="bgcolor: #CACACA, align: center"]41[/TD]
[TD="bgcolor: #CCFFFF"]JA[/TD]
[TD="bgcolor: #CCFFFF, align: right"]9/30/2013[/TD]
[TD="bgcolor: #CCFFFF, align: right"]7:00 AM[/TD]
[TD="bgcolor: #CCFFFF, align: right"]7:00 PM[/TD]
[TD="bgcolor: #CCFFFF, align: right"]9/30/2013[/TD]

[TD="bgcolor: #CACACA, align: center"]42[/TD]
[TD="bgcolor: #CCFFFF"]KW[/TD]
[TD="bgcolor: #CCFFFF, align: right"]9/30/2013[/TD]
[TD="bgcolor: #CCFFFF, align: right"]7:00 PM[/TD]
[TD="bgcolor: #CCFFFF, align: right"]7:00 AM[/TD]
[TD="bgcolor: #CCFFFF, align: right"]10/1/2013[/TD]

[TD="bgcolor: #CACACA, align: center"]43[/TD]
[TD="bgcolor: #CCFFFF"]Intern[/TD]
[TD="bgcolor: #CCFFFF, align: right"]9/30/2013[/TD]
[TD="bgcolor: #CCFFFF, align: right"]2:00 PM[/TD]
[TD="bgcolor: #CCFFFF, align: right"]2:00 AM[/TD]
[TD="bgcolor: #CCFFFF, align: right"]10/1/2013[/TD]

</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