I recently worked on a requirement for a project that at first appeared to be fairly simple. I decided to tackle it first thing in the morning. I had about fifteen minutes before my daily meeting so I thought why not go ahead and get it done so I could cross this off of my to-do list? I typically try to abide by the rule of working on my most difficult or challenging task when I am the most focused or energetic (morning time for me after a bit of caffeine). So I was breaking my rule, or so I thought…
My quick, ten minute task turned into an hour before all was said and done.
I was working on an anonymous Publishing site in SharePoint 2010 using a Content Query Web Part with a custom Item Style to display Discussion Board topics. I was using the OOTB Discussion Board.
The requirement was to add the ability for users to put an Expiration Date on their Discussion Topics so they could automatically be filtered out from the list of items in the web part. The requirement that turned this task into a not so quick one: Make sure users are NOT required to enter in an Expiration Date.
Here are the steps I took:
Step 1: Create a new column of type Date and Time in the Discussion Content Type called ‘Discussion Expires’. Do not require that the column contains information. Select Date Only as the format with no default value.
Step 2: Add a few Discussion Topics, enter some with an expiration date greater than today, and some with a date less than today. Make sure to also add a few that do not have a value for the Expiration Date. Then add in my filter on my CQWP to test it out. When I took a look at the web part settings of the CQWP, I quickly realized my new Discussion Expires column was not going to work due to the requirement that this field could be left blank. Unfortunately with CQWPs, you cannot filter on null values in a date field. You can in a normal view filter by just leaving the value empty. So now what? Well now it was meeting time, so this was going to have to wait for about a half hour.
Step 3: Back to my task at hand. I decided to create a calculated column titled ‘Discussion Expiration’, set the formula to be equal to my ‘Discussion Expires’ field, and set the data type returned from the formula as Date Only:
I then took a look at my List View to see what the data looked like in this new calculated column:
SharePoint will return a value of 12/30/1899 for empty/null date values in a calculated column. Unfortunately trying to use this in a filter in either a CQWP or a normal SharePoint view filter will not return any items:
SOLUTION: Convert the column to text. I created a calculated column titled ‘ExpirationCalc’ and used the following formula:
This worked like a charm. Here were the values returned in my List for my new calculated column of type text:
I could now use the following filter in my Content Query Web Part:
Now the Web Part will show all Discussions Topics that are not expired based on the date provided or were not given a value for the Expiration Date.
Now that I had that working, I questioned the obvious. Was it really necessary to create two calculated columns to get the results I wanted? For my scenario, it wasn’t. The formula is best to use if you are already using a calculated column to work with the values in your regular date column and some return null or 12/30/1899. If not, you can make this work with just one additional calculated column of type text. Here is the formula I ended up using:
=IF(ISBLANK([Discussion Expires]),"Never",TEXT([Discussion Expires],"mm/dd/yyyy"))
I’m working with my original date column called Discussion Expires and setting it to 'Never' if the user does not enter a value, otherwise setting it to the value entered by the user. Setting blank values to a default value of Never was only done because I was also displaying this column in the Content Query Web Part and did not want the users to see any empty space which made it appear as if data was missing. So for my filter on my CQWP, I could now use: Show items when Discussion Expires is greater than [Today] or DiscussionExpiration is equal to Never.
If you do not need to set a default value, the following formula will work:
=IF(ISBLANK([Discussion Expires]),"",TEXT([Discussion Expires],"mm/dd/yyyy"))