Creating a Who’s Out Now SharePoint Calendar



Frequently organizations without SharePoint send out blast emails to the entire organization. One of the most popular that I have seen is “Bob Smith” will be on vacation from x date to y date. Now granted to his department this may be a very important communication, but the rest of the organization doesn’t care. And if they do care, it isn’t likely they will remember his email anyway. One way I have solved this problem is with a shared SharePoint calendar for people to post their vacations on.

While this works, and its fairly easy to connect a SharePoint calendar to outlook, people still have to go the calendar and look at today’s date to see who is out. Depending on the view (calendar or list) this could be difficult if more than a few people are out.


“Wouldn’t it be cool if there was a web part that would show me only the people that are out right now?”

How it’s Done

So you are probably thinking like I did, ok sweet I’ll just create a filtered view that only displays calendar appointments where the start date is less than or equal to today and the end date is greater than or equal to today.

Well this won’t work. SharePoint doesn’t allow you to compare date fields on a calendar…..or Does it


Its actually pretty easy. I’m not sure how stable this is or if the upgrade to 2010 will break it, but it works for us.

  1. Create a calculated column that is equal to the start date. In this example, we are going to call it CompStart (becuase we are comparing the start time)
  2. We then do the same thing for the end time
  3. Now we can create a view called “Whos Out Now” with the following filter

Now all you have to do is add the calendar web part to the page of your choosing, and select the Who’s out now view for the calendar. I like to add a little polish to it by uploading a calendar icon to a picture library, and using the title icon image url in the web part properties.


End Result


Please feel free to leave your comments!


All about custom themes and styles in Microsoft Word 2007



My organization is a small government agency with a large number of non-technical computer users. They do have a great desire to produce professional official looking documents. I noticed however, that despite their best efforts, there was little consistency between memos, and frequently staff spent more time formatting a document than writing it. Because of this, I came up with the idea to use Word 2007’s ribbon interface and office theme-ing to predefine document elements and style, but still allow some end users choice.


  • Use the native office 2007 tools to create a consistently styled document very quickly

    This will include:

    • Heading styles
    • Custom headers and footers
    • Custom cover pages
    • Custom color pallet
  • Retain the office 2007 “formatting preview” feature
  • No custom code

First approach

On our first attempt, our graphic artist mocked up the font selection, headings 1-6 headers, footers, and cover pages. One of my main requirements was that you could retain all of ms word’s formatting preview features.

Next we created two main document templates (.dotx) One that would contain the formatting elements and colors for a more vibrant look, and one that would be more official and used for regulatory reporting.

Then we created custom headings 1-6 by formatting text, highlighting it, then using the styles gallery to save selection as a new quick style. We named all of the styles appropriately for headings 1-6. We continued to do the same approach for creating the cover pages, headers footers, and document snippets. Most of these were created as building blocks.


While the cover pages, headers and footers worked great, the styles would not “preview format” and we could not change the formatting of the document by selecting a new Quick Style Set. Additionally SmartArt colors were way off and retained the default “MS Blue”

We Had a Lot to Learn

After a great deal of research and testing we discovered, that we were only using one of the several required technologies built into MS Word to accomplish my goals.


In order to understand all of this you really must speak “blue” (Microsoft Language)

Theme – Combination of color, font and effect, found in the page layout ribbon

Quick Style – A predefined style of text found in the styles section of the home ribbion

Quick Style Set – A combination of several quick styles that can be used to change the formatting of an entire MS word document. These are viewable by selecting change styles in the styles section of the home ribbon. MS word contains 11 quick style sets out of the box.

Building Block – The MS Word Team Blog defines building blocks simply as a reusable chunk of a word document.

How to Really Use the Quick Style Gallery

To apply document branding using all of MS Word’s features you must use a combination of Themes and Styles.

Best Practices

  1. Use themes to define:
    1. Font selection (not size or formatting….just the font itself)
    2. Colors in the document
  2. Use Styles to Define:
    1. Font size
    2. Font formatting (bold, italics, underline …etc
    3. Block formatting or underlining of a entire document heading (ex. A filled box around an entire document heading)
    4. Color of font, from the colors defined in the theme.
      If you need to change the color of all headings, then change the order of colors in your color pallete. I.E switch Accents 1 and 2.
    5. Apply as few formatting changes in the style gallery as absolutely possible to achieve your look.

  3. Start with a default quick style set and then save as your own
    1. This is required in order for the “formatting preview” to work
    2. Switch color order in the theme to make this work for you.
      The default quick style galleries use Accent 1 and Accent 2 for most of the underlining and text colors. By creating your own color pallete and switching the order of accents 1, 2 and three you can complete the color formatting very easily.
  4. You can apply font selection and color independently of a whole theme, but why would you.

Files and technology


A theme in a MS Word document consists of a color pallete, Header and Body Fonts, and effects. This theme consists of four files. A .thmx file and three .xml files. The .thmx brings together the color, font, and effect (.xml) files into a theme (.thmx)


C:\Users\%username%\AppData\Roaming\Microsoft\Templates\Document Themes



Directory Tree:

Theme Name.thmx

(Folder) Theme Colors


(Folder) Theme Effects


(Folder) Theme Fonts


Quick Style Galleries

This is one that I really had trouble understanding because it is so easyJ A quick style gallery is essentially a combination of quick styles stored in a .dotx file stored in the following location:

C:\Users\%username%\AppData\Roaming\Microsoft\Templates\Document Themes

This is only for the user defined Quick Style Galleries, the default office quick style galleries are stored:

C:\Program Files (x86)\Microsoft Office\Office12\1033\QuickStyles

Conclusion and Final Thoughts

In order for everything to work the way I wanted, we ended up with the following

One document template (.dotx) containing all of the cover pages, headers and footers, and building blocks

Two themes, one with a formal color pallete and font selection and one with more vibrant colors

Two quick style sets, one with underlines and one without. This allows some individual expression but retains the font and colors as defined in the themes.

This was certainly well worth the effort and was quite an education in the capabilities of Microsoft Word. In my next post I will cover how to deploy these files as part of a corporate document branding project.

Cool office plug in

>I am a big fan of office 2007. I think that it is the greatest office suite ever made by any manufacturer and until you get into the very advanced functions, it is also very intuitive. Frequently however, I am on computers that may not have ms office installed and still need to view or edit a document. Since MS doesn’t have an online office suite yet, only an online storage service, google docs is about the only offering that fits the bill. After a little bit of searching today I found an interesting plugin for office that lets you save directly to google docs. I haven’t checked it out yet but it looks pretty cool

If anyone tries it out let me know your experiences!


Free iSCSI Target for Testing ESX Server

Jason Boche over at has a great blog post on creating a free iSCSI storage target for use with ESX server. It is definitely worth a read for those trying to get up to speed on ESX and need a test environment.


Comparing Last Year Period to This Year in Crystal Reports

>This was one of the toughest things I had to figure out in Crystal because there are very few examples out there on the internet on how to have columns in a report that compare the previous year values to this year.

The challenge:
My CFO wanted a report that showed waste receipts (we are a landfill) that had several columns that looked something like this:

Last Year to date
This year to date
Variance in tons
Variance in %
Previous Year Last 30 Days
This Year Last 30 Days
Variance in Tons
Variance in Percent
Total Receipts Last Year
Forecast Receipts for this year

So I thought how in the world do you do this because crystal only has one detail section and in my head I couldn’t figure out how you would compare two years values with only one query. Here is what I found.

Crystal has built in functions to calculate what period a date is in. Which is great but the logic on how to apply it was escaping me. What I ended up doing is a detail section that looks like this:

I only needed a couple of fields from the database record, the date of the Transaction [TimeOut] the Transaction number [Ticket] and the Tons Received [Tons]

I had to hide the Tons field because we are going to use this for calculations but since a total on tons would be a total for all query results there is no need to use it. Then for each of the period fields I created formula fields using the build in period functions of crystal for example Last Year to Date Tons [LYTD_Tons] is

If {Ticket.TimeOut} in LastYearYTD Then

So now I have a tons field that will only display a value when the date of the transaction is in last year to date. Otherwise this value will be a zero. By applying this same logic to the other period fields I can create summary fields that sum all of the period based fields. I placed this summary in the group for each customer. So what I get is a row on the report that shows Customer Name, LYTD, PYMTD, MTD, LY Total. Now that I have these fields I can create additional summaries that calucate the variance from last year.

The end result looks something like this:

In my humble opinion this is truly a work of art. If you have questions or would like more detail please add comments


Crystal Reports Date Difference


I have searched and searched over the internet many times trying to perfomr the simple task fo calculating the difference between two dates in Crystal Reports. The result I was looking for had to be formatted in DD:HH:MM:SS. After hours of searching here is how I did it.

Create a formula field that gets the total number of seconds between the start date and completed date:

DATEDIFF(“s”, {Incidents.Start_Date},{Incidents.Completed} )

I then used a second formula field to perform the arithmetic to calculate the remainders and format the output:

NumberVar TotalSec := {@TotalSeconds};
NumberVar Days := Truncate (TotalSec / 86400);
NumberVar Hours := Truncate (Remainder ( TotalSec , 86400) / 3600) ;
NumberVar Minutes := Truncate (Remainder ( TotalSec , 3600) / 60) ;
NumberVar Seconds := Remainder (TotalSec , 60) ;
Totext ( Days , ‘##’ ) + ‘:’ +
Totext ( Hours , ’00’ ) + ‘:’ +
Totext ( Minutes , ’00’ ) + ‘:’ +
Totext ( Seconds , ’00’ )

There that works!! It seemed like it took forever to find the answer to this problem!
Up Next is how to do this based on a work calendar!