A progress bar for your tasks list and Printing

Today, let’s see how to show the progress of your tasks in SharePoint:

We’ll rely on this method: using calculated columns to write HTML. Easy, and entirely done through the SharePoint UI.
The [% Complete] is a default column in tasks lists. We just need to add - in a calculated column - the formulas for this specific use case. Here they are:
- Progress Bar:
=”<DIV style=’background-color:blue; width:”&([% Complete]*100)&”%;’> </DIV>”
- Progress Bar 2:
=”<DIV style=’background-color:red;’><DIV style=’background-color:LimeGreen; width:”&([% Complete]*100)&”%;’> </DIV></DIV>”
Note: the   character is mandatory to make this work in Firefox.
If you are looking for other colours, here is a very good reference:
http://www.w3schools.com/html/html_colornames.asp


Printable progress bars

You may have noticed that when you print lists including my examples of progress bars, the colours are not rendered. This is actually the expected behaviour: when printing Web pages, background colours are usually ignored by the browser.
So what can you do if you want to print your progress bars?
A first possibility is to modify your browser settings to print backgrounds. In Internet Explorer for example, follow this path:
Tools | Internet Options | Advanced | Printing.
A second option is to modify the formula and use colors instead of background colors, or use images. Here is an example of workaround for the above screenshot, where I used border colors (instead of background colors in the original post):
view plaincopy to clipboardprint?

="<DIV style='position:relative;'><DIV style='font-size:0px; border-top: 14px solid "&CHOOSE(INT([%]*10) +1,"red","red","OrangeRed","OrangeRed","DarkOrange","Orange","Gold","yellow","GreenYellow","LawnGreen","Lime")&"; width:"&([%]*100)&"%;'> </DIV><DIV style='position:absolute; top:0px;'>"&TEXT([%],"0%")&"</DIV></DIV>" 

="<DIV style='position:relative;'><DIV style='font-size:0px; border-top: 14px solid "&CHOOSE(INT([%]*10) +1,"red","red","OrangeRed","OrangeRed","DarkOrange","Orange","Gold","yellow","GreenYellow","LawnGreen","Lime")&"; width:"&([%]*100)&"%;'> </DIV><DIV style='position:absolute; top:0px;'>"&TEXT([%],"0%")&"</DIV></DIV>"
As usual, feel free to share your own solutions!
This behaviour was first reported to me by Peter Allen

SharePoint Calculated Columns :: Complex String Field Calculations

This formula uses some xfields generated with the Workflow engine. Beware of Content approval and workflow as the new xfields will continue to set approval back to Pending.

="Room: "&xRoom&" at "&TEXT([Start Time],"h:mmam/pm")&" for "&TEXT(Length,"#0.00")&"hrs ["&TRIM([Whats Happening])&"] "&IF([Number of Students]>0,"Capacity: "&[Number of Students]&"People. This is a ","")&IF(xRecur="True","Repeating Booking-"," ONEOFF Booking")&" and is "&MID(xApproval,4,10)
=IF(xRoom="@COMMON",[Whats Happening],UPPER(MID(xRoom,1,5))&" | "&LOWER(MID(xWho,11,15))&" | "&UPPER([Booking Status])&" | "&TEXT([Start Time],"h:mmam/pm")&" | "&TEXT(Length,"#0.00")&" hrs | "&TRIM([Whats Happening])&" | "&IF([Number of Students]>0,[Number of Students]&" Pple.","")&IF(xRecur="True","| REPEATS"," | ONEOFF"))

How to Install SharePoint 2013… for the first time…by Todd Klindt (Reposted here)

Hey everyone,

Todd Klindt is like the guru of SharePoint and he is at the leading edge of all this stuff.

If you have a lab or test environment with some VM’s somewhere not doing anything then have a go at doing what Todd recommends in his blog.

Its a really long URL so I shortened it with Bitly but I swear I don’t get anything for sending you Todd’s way he is just a great teacher.

http://bit.ly/X2e24j

Visualization – calculated color gradients..by Mark Milner

If you need dynamically generated visualizations for your SharePoint data, have you considered leveraging the power of the Calculated Column? This Tuesday at 1:00pm EST, Mark Miller and I will give you all the keys to master this simple yet powerful technique. At the end of the two hour entry level workshop, you’ll be able to add color coding, KPIs and other effects – like the one described in this post – to your SharePoint lists.

 

Green/Yellow/Red is a standard color palette for dashboards. You can just use 3 colors to visualize discrete states, for example the status of a project (on track – drifting - late). But if your purpose is to communicate progress, or a measure on a scale, you need a larger color palette. This is for example the case in my screenshot, where the color reflects the level of completion (in %), in a tasks list.

So, how can I do this in SharePoint? Of course, my plan is to use a calculated column that will determine the color, based on the value in the [% Completed] column.

Method 1: nested IFs

This is the most basic approach:
if [% Completed]>90, select green
else if [% Completed]>80, etc.
I am not going to detail it, as we can do much better.

Method 2: CHOOSE function

The CHOOSE function is more elegant than nested IFs, and is a natural choice when dealing with multiple options. You’ll find all the explanations to achieve a color gradient in this post.

Method 3: pure calculation

So, how can we go even further? Well, colors can be identified by their name, but also by theirrgb code, as each color can be generated from a combination of red, green and blue. For example:
red: rgb(255,0,0)
green: rgb(0,255,0)
blue: rgb(0,0,255)
yellow: rgb(255,255,0)
white: rgb(255,255,255)

Using these values, we can “easily” create our red/yellow/green gradient:
rgb(255,0,0) –> rgb(255,255,0) –> rgb(0,255,0)

The following formula, entered in a calculated column, will give you the rgb value for each value of the [% Completed] column:
=”rgb(“&INT(MIN(510-[% Complete]*255*2,255))&”,”&INT(MIN([% Complete]*255*2,255))&”,0)”

To obtain the visual effect as in the screenshot, use the HTML Calculated Column method, with the following formula:

1
="<span style='display:inline-block;position:relative; width:60px; height:14px;border:1px solid;'><span style='display:inline-block;position:relative;background-color:rgb("&INT(MIN(510-[% Complete]*255*2,255))&","&INT(MIN([% Complete]*255*2,255))&",0); width:"&([% Complete]*100)&"%;height:14px;'><span style='position:absolute; top:0px;'> "&TEXT([% Complete],"0%")&"</span></span></span>"

SharePoint Calculated Columns :: Start and End Times

My custom CT (content type) is called Process Step. (The parent CT is Issue.) The purpose of the list is to allow loading of a series of steps (the same set for each month) and track the time it takes to complete each step, focusing on those identified as "key steps."
As I said, I added "start time," "end time" and "duration" to my content type. Start time is a date/time field which populates off a WF that uses "set list item" action to drop the date/time corresponding to "modified" which occurs when the item status changes from "not started" to "in progress." The same thing happens when status changes from "in progress" to "completed" - another WF updates "end time" to copy over whatever was the "modified" date/time matching that change.
"Duration" is a calculated column derived from end time minus start time, single line of text type, using this formula:
=TEXT([End time]-[Start time],"h:mm:ss")
The result of the calculation appears in the hours:minutes:seconds format
I want to provide a way to measure the duration against a target duration; therefore, the values I enter for each step as my target duration must also be in the hours:minutes:seconds format- correct?
Assuming that can be created, I then want one more calculated column )"duration comparison") which will compare the actual duration with the target duration. I am fuzzy on how to display this, however... I'm not sure what the best comparison format would be (a difference, a ratio, not sure). So I don't know what type of field to use. I think it will depend on how the target duration field is formatted.
Finally, I will create a view to display only the "key steps" and set up a KPI that lets me see the key steps' duration comparison values.
Is that the info you need?


Ok, starting to make sense now.
Everything sounds good so far...the "Duration" column should be returning a value in the "h:mm:ss" format (which it sounds like it is).
So, to add in a "Target Duration", you can just create a new "Single line of text" column and literally enter in the values in the same "h:mm:ss" format (i.e. 2.5 hrs would be 2:30:00). Do this for each step you want to track. (Q: are there any issues with manually entering in this data?)
For the "Duration Comparison" column, just use a Calculated column with a return type of "Single line of text" and a formula something like:
=IF(TEXT([Actual Duration],"HH:MM:SS")<TEXT([Target Duration],"HH:MM:SS"),TEXT([Target Duration]-[Actual Duration],"HH:MM:SS")&" Under",TEXT([Actual Duration]-[Target Duration],"HH:MM:SS")&" Over")
This formula would be read as: "If the text of the 'Actual Duration' column formatted as a standard 'hh:mm:ss' time format is less than the text of the 'Target Duration' column formatted as a standard 'hh:mm:ss' time format, subtract the 'Actual Duration' column from the 'Target Duration' column formatted as a standard 'hh:mm:ss' time format, followed by the text 'Under'. If not, subtract the 'Target Duration' column from the 'Actual Duration' column formatted as a standard 'hh:mm:ss' time format, followed by the text 'Over'."
This would display (using example data):
Start Time: 9:00:00
End Time: 9:45:00
Actual Duration: 00:45:00
Target Duration: 1:30:00
Duration Comparison: 00:45:00 Under
Getting a bit trickier, you could use an alternate formula of:

=IF(TEXT([Actual Duration],"HH:MM:SS")<TEXT([Target Duration],"HH:MM:SS"),HOUR([Target Duration]-[Actual Duration])&" hrs "&MINUTE([Target Duration]-[Actual Duration])&" min "&SECOND([Target Duration]-[Actual Duration])&" sec Under",HOUR([Actual Duration]-[Target Duration])&" hrs "&MINUTE([Actual Duration]-[Target Duration])&" min "&SECOND([Actual Duration]-[Target Duration])&" sec Over")
This would display it as:
Start Time: 9:00:00
End Time: 9:45:00
Actual Duration: 00:45:00
Target Duration: 1:30:00
Duration Comparison: 0 hrs 45 min 0 sec Under
This one parses out the individual hours/minutes/seconds into a more "textual" display rather than the "hh:mm:ss" style display, but either version would work.
The custom view portion should be pretty straight-forward, but does this help with the rest?

SharePoint Calculated Columns :: Getting TODAY as a Calculated Column in a Custom List

You can trick SharePoint into creating a column for today’s date which you can then compare today’s date to another date (in this case revised date).

As a result, I created a column for “Today”, with single line of text and then created another calculated column “CalcToday” and set it equal to the “Today” column.

Once I was done I deleted the “Today” column and voila! You have a column which is set to the current date!

Then I just created the “Days Overdue” column which compares date information from certain items to be addressed/completed (a.k.a”Revised Date”) to “CalcToday”.

Free SharePoint Website for trying stuff out

I know there is now Office 365 accounts but sometimes you don’t want all the clutter of that product.

So here is a free website you can play with. Enjoy

http://www.freesharepoint.com/