SharePoint Color Coded Calendar – The PayRoll Schedule
- SharePoint Color Coded Calendar – The PayRoll Schedule
- Screencast: Color Code a SharePoint Calendar – The PayRoll Schedule
- 7 Minute Screencast: Color code a SharePoint Calendar – Update
Update: Jim Bob has sent a 17 minute screencast of his solution which includes another way to assign colors.
After watching Mark Miller’s Screen-Cast, Color Code a SharePoint Calendar, I realized this was the perfect solution for transforming a payroll schedule on an Excel spreadsheet into a SharePoint Calendar to make it more visually appealing and user-friendly.
First Things First
Since I was beginning with a simple list of dates that was already color-coded, converting it to a calendar following Mark’s screen-cast was a snap!
Payroll Schedule in Excel
Of course, the first task was to create a SharePoint Calendar, in three easy steps:
1. Create
2. Create Calendar
3. New Calendar
Presto!
Basic Payroll Calendar
Show Me the Money
The basic calendar is nice and you can display lots of useful information using it this way. But the payroll calendar is something that folks will look at often and the most important question is When do I get paid?
So, let’s create some fields that will help us make Payday stand out, while providing color-coded information about some other important dates, as well.
Setting up the Columns
From the Payroll calendar we just created, click Settings > List Settings:
List Settings
Here, you will see I have already created some columns we will use to make our color-coded calendar.
Customize Columns
Let’s go through the basic columns and then we’ll expand on the more advanced ones:
- Pay Period: This is where we will store the number of the pay period. 1 is for the first payday of the year (though the pay period will most likely begin in the previous year), 2 is for the second, and so on. In our example, there are 26 pay periods per year, so when creating this column, I set a range of 1 to 26, just to avoid accidentally keying a wrong number.
- Start Time and End Time: These come with the Calendar list and we’ll put them to use for our calendar, as is.
- Title: This is the default field for every SharePoint list. We won’t be using it, so I made it not required.
- Location and Description: Again, we won’t be using these, but they’re standard calendar fields that cannot be deleted.
- Three other fields are standard calendar fields that will show up on the EditForm: All Day Event, Recurrence, and Workspace. For our purposes, we’ll use All Day Event and ignore the other two.
Advanced Fields
Now we move into the specific fields we’ll use to (a) make it easy for HR to update the calendar and (b) let their choices be automatically color-coded so the employees can easily find the information they need.
DateType
In the spreadsheet above, we have two dates for the Pay Period: Beginning of Pay Period and End of Pay Period. We’ll combine these into one event and use the Start Time and End Time to show the beginning and ending dates.
Thus we’ll have three choices for DateType: Pay Period, Payday, and HR Deadline. (HR Deadline is the deadline for making any changes that will appear on that pay period’s payday.)
Column: DateType
We will be using the CHOOSE function to FIND a common letter in our DateType choices that is in a different location for each choice. Each of the choices shown has a lower case a, but that won’t work for us, since it is in the same location (the second position) for two of our choices: Pay Period and Payday. So, we’ll use the lower case d, which appears in each choice, but in a different position for each one.
Note: If your choices are different, you will need to look for a common letter to FIND. Alternately, multi-word phrases might allow you to search for a space, or you could force a difference by adding a number at the beginning of each item. If your choices are different lengths, you could use the length, rather than searching for a character or number. Finally, you could add a calculated field that assigns a number to each record which you would then use for calculating colors. (For each of these options see the following posts, or see the comments on Christophe’s post referenced below.)
Color and BGColor
Next we’ll create two calculated fields, based on the DateType choice.
When I examine the Excel schedule above, I see that the capable folks in HR have already chosen some colors to help them differentiate dates. I’m going to use those same colors, converting them to their hexadecimal values.
To find the color they’ve used, I opened the color palette in Excel to see what they’re using. The easiest way to do this in Excel 2007 is to right-click on the field and follow the on-screen menus. In order versions, you can get to this by going to the Font menu (Edit -> Font) and clicking the font-color drop-down.
Determining color values
Clicking on More Colors… will bring up the Colors dialog box.
Custom Color Values
Click on the Custom tab and look at the Red, Green, and Blue values. These are decimal values and we need to convert them to one hexadecimal value (White, for instance is #FFFFFF in hexadecimal) per color.
Each RGB (Red, Green, Blue) hexadecimal value can be separated into three pairs of two-digit hexadecimal values which represent the Red, Green, and Blue values, respectively. Looking at our first color above, we need to convert the Red, Green, and Blue values from decimal (128, 0, 128, respectively) to three two-digit hexadecimal values. A quick and easy way to do this is using the Calculator application in Scientific view.
Scientific Calculator (Choose View -> Scientific)
Simply enter your decimal value (128, in this case) and click on the Hex radio button.
Hexadecimal equivalent of Decimal 128
This is your Red hex value (and Blue, too, in this example). Zero in decimal is zero in hex, too. So, your RGB value becomes #800080 (the # indicates that this should be read as a hex value).
NOTE: The highest two-digit hex value is FF. Decimal is a base 10 counting system, meaning there are ten digits [0-9] used to make all of the numbers in it. Hexadecimal is base 16, meaning there are sixteen digits [0-9, then A-F] used to make all of its numbers.
Doing this for the other two colors, we see that HR has chosen #800080 for Beginning of Pay Period, #008000 for Payday, and #800000 for Payroll/HR Deadline (4PM). We’ll use those same colors for our calculated Color column.
FIND and CHOOSE
Now, we need to determine which color to assign to each row based on the DateType. As stated before, the common letter that has a unique position in each DateType choice is lower case d. We’ll use the FIND function to locate that exact position.
I always have trouble remembering which arrays are zero-based and which are one-based. If you’re like me, I recommend taking an intermediate step to determine what value each of your choices will return. So, before completing the next step, I recommend setting your Color Formula to: =FIND("d",DateType,1) and then looking at the data in a list view. In our case, this will show that the "d" is in the 10th, 4th, and 7th positions, respectively, for Pay Period, Payday, and HR Deadline.
To make it easy to visualize, in the next step this number will be the total number of commas that come before each respective color in the CHOOSE function.
NOTE: If you miscalculate and end up with fewer CHOOSE options than the highest value FIND returns, you will get an error that will show #VALUE in this field. Simply take off the CHOOSE and perform the FIND for each choice (as recommended in the intermediate step two paragraphs above) and recalculate.
Column: Color
How this works: The FIND function looks for d in the DateType choice. It will return this number to CHOOSE function which will then set the Color to the value in that position in the CHOOSE function.
For example, in "Payday," the d is in the 4th position, so FIND returns 4. The CHOOSE function then becomes =CHOOSE(4,,,,"#008000",,,"#800000",,,"#800080). The fourth position has "#008000" so that becomes the value of Color for this row.
BGColor
We’ll use pretty much the same formula for BGColor, only we’ll set the background a little lighter but in the same hue as the text and border.
A simplified look at RGBs will show us that sliding all three values up by a corresponding amount will result in a similar hue, but much lighter as they approach white (#FFFFFF). For each of our colors, the RGB values are either half-way "on" (128 or #80) or "off" (0 or #00). If the Color value component is half-way (#80 is half of #FF) on, we’ll set the BGColor to all the way on (#FF); if it’s off, we’ll turn it most of the way on (#CC).
So, we’ll copy and paste our Color Formula to our BGColor Formula then change 80s to FFs and 00s to CCs (notice that hex values are NOT case-sensitive).
Column: BGColor
Some Assembly Required
Now we can put all of these element together in such a way that Christophe’s (Path to SharePoint) Text to HTML script can make the Content Editor Web Part do the color-coding we need. (Follow the link to get the code.)
Column: Display
Breaking this down, we’re creating a box around our text using the DIV tags (this is what Christophe’s code looks for), using the same Color value for the text and the border; and using the BGColor value for the background color of the box. Within the DIV itself, we’re combining the DateType and the Pay Period number as the text shown on the calendar for our event.
Entering the Data
We’re entering a good amount of data into this calendar, so let’s make it a little easier on ourselves by creating an appropriate Datasheet View of our list data.
From our Calendar, click on the View menu and choose Create View
Create View
Choose Datasheet View
Create View: Datasheet View
Choose to only display the fields you need for quickly entering the data. (I set mine up as a Private view. You might want to walk your editors through setting up a similar view so they can enter the next year’s data without your help.)
Edit View: Datasheet View Shown from Edit View so that selected fields are at the top for screen capture.
Payroll Calendar: Datasheet View
One day’s entry will look like this (if you have ordered your columns this way):
Payroll Calendar Event (Pay Period event)
A Calculated Effort
Now, the default view for a calendar shows the Title, but we didn’t even fill that in. We want it to show our calculated Display field instead.
To that, we’ll go to the View menu and click on Calendar -> Modify this View:
View -> Modify this View
Scroll down to Calendar Columns and notice that the default fields shown for each record are the Title and Location fields, which we left blank.
Calendar Columns
We want to change those to Display and DateType, respectively.
Changing the Calendar Columns
Click OK.
Almost There
Once you have all of your data entered, take a look at your Calendar.
Payroll Calendar – NOT Pretty
Even though we have all the data just the way we want it… we still have to tell SharePoint that we want the Display field to be considered HTML. We do that using Christophe’s code in the Content Editor Web Part. (He deserves the traffic and recognition for his code, so I’ll let you go get it from him. Once you have it, I’ll show you what to do with it.)
The Final Touch
From your Payroll Calendar, go to Site Actions -> Edit Page
Edit Page
Click on Add a Web Part at the top of the screen:
Add a Web Part
Under All Web Parts, choose Content Editor Web Part and click Add:
Content Editor Web Part
Click on open the tool pane:
Open the tool pane
Expand the Layout section and check Hidden:
Hide the CEWP
Then click Source Editor… and paste Christophe’s code in the box and click Save.
Drag the Payroll Calendar web part to the top, so the Content Editor Web Part is last on the page.
Click OK.
Click on Exit Edit Mode
Exit Edit Mode
Voila!
Color-Coded Payroll Calendar
NOTE: If you want to include this calendar as a web part on another page, simply make sure you add the CEWP to the end of that page, as well, with the same code from Christophe.
Guest Author: Jim Bob Howard
Jim Bob Howard is a web designer / web master in the healthcare industry. He has been working with SharePoint only since March 2009 and enjoys sharing what he has learned. You can email him at jimbobhoward@gmail.com.
I’m still having problems with this calendar. I get an ‘error’ message when I click ‘OK’. Hope some one can really help. I would like to be able to use for our ‘Training Calendar.’ Check comment #5 above. Here is the code I followed the step above.
=”"&DateType&”: “&[Pay Period]&”
Frank – see my comments 35.38.39
the keys for me were to create a NEW calendar rather than try and fix one that didnt work and to use Christophes code.
Persistence is key
Great kick starter! Thanks.
Fyi, the colors don’t have to be HEX RGB. There are plenty of standard colors defined which every browser supports today as part of HTML standards. A great reference site is http://www.w3schools.com and follow the links to CSS styles.
Great demo for static data. On the calculated fields, is there a way to update for every instance of the recurring appt or are you stuck with only the first date when the item was created?
The reason i am asking is we have a monthly meeting that quarterly gets additional members. Trying to show some differentation for the facilitators so they know that this will be a larger audience meeting on the calendar. So far i am having no luck doing so. Any help is greatly appreciated.
I really don’t understand the significance of the commas in the “Color” column.
=CHOOSE(FIND(”d”,DateType,1),,,,”#008000″,,,”#800000″,,,”#800080″)
Four comman, then three commas, then three commas. What does it mean, what is the significance? Can someone please explain this to me?
Dear Matt,
Yes, the significance lies with what the CHOOSE function is going to return.
Note the following paragraph above:
In this example, our CHOOSE function will only return 4, 7, or 10. The commas put responses in the fourth, seventh, and tenth positions. If there was a possibility that something else could be chosen, you might want to fill in the other positions with a “default” color, and/or check for an error and give a default color if ISERROR returns true.
I hope that helps!
Blessings,
Jim Bob
I followed Owen’s recommendation and created a new calendar and used Christophe’s HTMLToText v2.1 from the pathtosharepoint/download library, but had the same problem with items that were not all day items. They wouldn’t display properly in the week and day calendar views. Can you provide any suggestions to make it work?
Betty, a workaround is to make the following change:
var regexpA = new RegExp("\\s*< ([a-zA-Z]*)(.|\\s)*/\\1?>\\s*");(remove the last $ in the expression)
This will be fixed in the upcoming version 3.
Note for the participants in the visualization workshop: the Web Part you have received includes this modification.
Christophe,
Thanks! The correction made it better. Now it works in Month and Day views; the week view is also improved, but is not quite there yet. It doesn’t show the script anymore, but it only shows the time in the default Microsoft pale blue box. Doesn’t show the color or the contents that the display column is set to show–(=”"&DateType&”")). I double checked the calendar view setting and the week view is set with Display as the Week View Title with DateType as the subHeading. Any ideas?
I’ll look into it. Feel free to e-mail me more details (screenshot, formula, etc.):
christophe@pathtosharepoint.com
btw it works fine in my live demo:
http://www.pathtosharepoint.com/Calendars/Lists/WorkshopCalendar/calendar.aspx
Christophe,
Just wanted to check that you got my e-mail with the details you asked for.
Hi Betty,
I did, and sent you this reply the same day:
the first change is to use span tags instead of div tags, as explained here:
http://pathtosharepoint.wordpress.com/2008/11/15/sharepoint-calendars-color-coding-hover-effects-etc/
Hi, I followed every step and replicated everything. I just have a an issue regarding this, when I view the calendar in weekly/day, the coede doesn’t work.
can anyone help me solve this?
For those who can’t make it work in week/day view, here is a summary of the previous episodes:
1/ check out the original article and the updates:
http://pathtosharepoint.wordpress.com/2008/11/15/sharepoint-calendars-color-coding-hover-effects-etc/
In particular, make sure you use span tags, not divs.
2/ grab the latest version of the text to html here (and bookmark the page for upcoming updates):
http://www.pathtosharepoint.com/HTMLcc/default.aspx
3/ if it still doesn’t work, see the little tweak in comment 60 above (this will be fixed in the upcoming version 3)
4/ if it works, let us know!
4/ and if it still doesn’t work, send me more details:
christophe@pathtosharepoint.com
Hi,
I am having the same problem as previously mentioned (by Betty) in a calendar. The color coding works great in all views (day/week/month) for the items marked as “ALL DAY EVENT”, however, for the items that have a determined START TIME and END TIME, the item is in pale blue (rather than the color I assigned) and is displayed with the “START TIME” that is clickable (rather than the “title” I had set to be clickable).
I have done all of the below as posted by Christophe earlier, so I am looking for further assistance.
“For those who can’t make it work in week/day view, here is a summary of the previous episodes:
1/ check out the original article and the updates:
http://pathtosharepoint.wordpress.com/2008/11/15/sharepoint-calendars-color-coding-hover-effects-etc/
In particular, make sure you use span tags, not divs.
2/ grab the latest version of the text to html here (and bookmark the page for upcoming updates):
http://www.pathtosharepoint.com/HTMLcc/default.aspx
3/ if it still doesn’t work, see the little tweak in comment 60 above (this will be fixed in the upcoming version 3)
4/ if it works, let us know!
4/ and if it still doesn’t work, send me more details:
christophe@pathtosharepoint.com”
Please let me know any information required; I would be happy to provide code/screenshots.
Thank you,
Angela
Angela – Christophe has setup a forum on Stump the Panel to handle questions about the color coded calendar solutions. Post your question there and he’ll get right on it. — Mark
Thank you, Mark.
Under Christophe’s Forum: Path to SharePoint Workshop Solutions, I found the Forum: SharePoint Calendar Solutions, with 1 topic posted: Calendar Month View Add Fields. I have not found an exact forum setup to handle color coded calendar solutions. Shall I post my question as a new topic under the Forum: SharePoint Calendar Solutions? I just want to make sure I don’t start something new when a topic already exists.
Thanks,
Angela
Angela, that would be the right place. However, I am surprised that my instructions from November are not enough. I have also responded to the e-mail you sent me directly.
Thank you Mark and Christophe for such quick replies. I made an error when changing the DIV to SPAN. I have corrected this, and all events (intra-day included) are color coded ~~thank you! But now have the “strange wrapping issue” as presented by Susan earlier. My apologies if a solution is posted to this and I am overlooking it. I am trying to follow all of the posts to avoid bugging you with redundant questions.
Thank you,
Angela
Susan, the wrapped issue is not due to the method, and you’d have the same issue with normal text (for example a long title).
Ben’s comment to use DIVs is technically not correct, although it works in most cases. Actually, my first post (in 2008) used DIVs, but I later updated it to SPANs.
If you’d like to continue the discussion, please follow Mark’s advice and use the forum. This way you may also get input from other users.
This is driving me bananas, I have followed the screencast, seem to be recreating the columns and code correctly but can’t seem to reproduce this. Not sure what is going on but I am ready to bang my head on the desk. I am sure I have a comma in the wrong place in my expression or some such.
Kris – Try Stump the Panel. Jim Bob is on there everyday and you might convince him to walk you through it. — Mark
Post your formulas here and we’ll try to help you debug.
Blessings,
Jim Bob
Initially I tried this:
=”"&Title&”"
This gave me syntax errors over and over despite moving code here and there.
which didn’t work so I tried just using the formula from Christophe’s site which gave me this:
=” “&Title&”"
still no dice
I copied the code from Christophe’s website for the CEWP and pasted it into Source then placed it below the calendar as directed. Modified the views several ways. Just can’t get it to work.
First of all, make sure your quotes are not smart quotes.
They aren’t; they are coming through on this post as smart quotes for some reason, sorry.
Great, great post! Thanks Jim Bob, Christophe, and Mark.
I was able to modify the steps to set up a color coded calendar to track personnel activity (vacation, sick leave, FMLA, LWOP). Because there aren’t any common letters in any of those choices I used IF statements in the calculated columns as follows:
ActivityType:
Vacation
Sick Leave
FMLA
LWOP
Color:
=IF(ActivityType=”Vacation”,”#ccffcc”,IF(ActivityType=”Sick Leave”,”#ffcccc”,IF(ActivityType=”FMLA”,”#ffccff”,IF(ActivityType=”LWOP”,”#ffcc99))))
BGColor:
=IF(ActivityType=”Vacation”,”#008000″,IF(ActivityType=”Sick Leave”,”#800000″,IF(ActivityType=”FMLA”,”#800080″,IF(ActivityType=”LWOP”,”#)008800)))
I changed the Display field so that it displays as Title:ActivityType:
=”"&LeaveType&”: “&Title&”"
Added Christophes script and voila I have a color coded Personnel Activity calendar. Something I have been hearing screaming about for quite some time.
Thanks guys, the info is much appreciated!
Not sure why the last piece of code didn’t post there. Should be as follows:
=”DIV style=’border:1px “&Color&” solid; padding:5px; margin:0px; color:”&Color&”; background-color: “&BGColor&”;’>”&ActivityType&”: “&Title&”</DIV"
Might have been the DIV's Add before and after the DIVs as appropriate.
Hi,
Great post. I got all the way to the end and I have the code appearing, but when I put in Christophe’s code it didn’t make a color box, it only displays the code. I made sure to move the CEWP to the bottom. Can you help?
Here is what it shows on the calendar rather than a nice colored box:
Committee Meeting: HP Meeting
It looks lile my code didn’t send through my last post.
Christophe,
Thanks for the update. I’ll be posting a reference to your new article some time today.
Mark