Excel Shift Work Roster Exporting to Google Calendar (or similiar)

[eazeuptiger]

Good evening,

I manage a nursing shift roster for about 160 odd staff. I use excel to build and publish the roster and am currently looking at trying to export to a google calendar (or similiar).

We use text code for the shifts (i.e. E = 0715-1545 & LM = 1445-2315). Currently the shifts are displayed on one sheet and I was thinking a use a formula on another sheet to LOOKUP the code and change to the relevant time to then export to google calendar.

Or if anyone has any other suggestions.

 

[Perpa]

_tiger,
I did something similar a few years back, however the number of staff was considerably smaller.
Here is a screen shot of what the assignment sheet looked like for that hospital. Notice that there were
3 shifts per day, 7 days a week.

Once the shifts were assigned, I used a macro to transpose the data into the proper format then save it in CSV (Comma Separated Values) format that could then be uploaded to Google Calendar. Below is a screenshot of a portion of how the data was transposed, and then what that looked like as a CSV file:

The key to success with this approach is the headers in your CSV file. All CSV files must have a correctly formatted header. The minimum amount of header information required to import events into Google Calendar is: Subject and the Start Date. With that header information, 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.

In my example I used 'Subject, Start Date, Start Time, End Date, End Time', where 'Subject' was the initials the person or persons working that shift.

Depending on the layout of your starting sheet, a macro could be created to transpose that data to the needed format on a second sheet, then save the second sheet as CSV.
Good Luck!
Perpa

 

[eazeuptiger]

Thank you Perpa.

This is great information - but I am unsure of the workload to develop. I say this as I have as previously mentioned about 160 staff. We roster for a four week period covering 24/7 (as expected) and there are approx 15 different shift codes.

Thoughts?

 

[Perpa]

_tiger,
Google Calendar needs to see the DATE and TIME in a familiar format. So to start with, you would need to have a START DATE for your calendar similar to the YELLOW filled cell (B2 in my example posting). If your Week 1 on Sheet1 begins on Monday, and is not the 1st day of the current month, then enter whatever date Monday is from the previous month. That gives you an initial START DATE...and a point of beginning each month.

The 15 different shift codes would need to be in a recognizable TIME Format. So for instance, on Sheet3
you could have a lookup table something like the following (using a colon with the 24-hour format):
(The underlining was used to maintain spacing for viewing this post as separate columns)

Code___Start Time___End Time

E_______7:15________15:45

LM_____14:45_______23:15

ON_____22:15_______6:30 This is an overnight shift so the End Date is 1 day more than the Start Date

....and so on for all 15 Shift Codes.

With the Month START DATE and the SHIFT CODES it is then possible to create a list similar to the one I have shown in my original post, that can in turn be saved in CSV format for uploading to Google Calendar. Sheet2 can be accomplished using macro code, but must be tailored to your setup on Sheet1. But it is still a manual process to upload the CSV file to Google Calendar. You must decide if it is worth all that each month.

Since you have so many staff members it must be quite a task each month just to coordinate with each of them and then enter the desired shifts on the dates they are available...that is the most time consuming part of this whole process. Not to mention any rules regarding working consecutive shifts! Quite a daunting recurring task to be sure.
You must be a 'tiger' to tackle this job!
Perpa