Wow! I never expected to write so many posts about Column Validation. Yet, here I am with the 5th post in what has become a series about Column Validation. Just to recap, here are the links to my previous posts on Column Validation:

•  How to Validate a Due Date in a SharePoint 2010 List
•  How to Validate Strict Text Formats in SharePoint 2010
•  How to Validate an Email Address Using Column Validation in SharePoint 2010
• How to Use ‘OR’ Type Options in SharePoint 2010 Column Validation

Also, if you would like to learn more about these techniques, with live demonstration and discussion, I’ll be speaking on Column Validation at July. 2012 meeting of the Federal SharePoint User’s Group (FEDSPUG) in Washington, DC. Come on out, I would love to meet you!

The Problem

A reader asked, “I have form that does not require the ‘phone’ to be filled in. However I want to validate the entry if/when it is filled in. Is there a way to add string at end of the phone validation formula to accept a ‘null’ value to avoid validation error?” Yes, this is possible, and in this post I’ll show you how to do it.

Read the rest of this entry »

I didn’t originally set out to write a series of posts on Column Validation in SharePoint 2010, but I keep thinking of new challenges each time I do. I have no idea how long this series may go (in fact, this may be the last one), but this is the third in the series. Here are links to the previous two posts:

1. How to Validate Strict Text Formats in SharePoint 2010
2. How to Validate an Email Address Using Column Validation in SharePoint 2010

In today’s post we’ll look at how we might allow users to enter data in a one of a couple different formats. The example that comes to mind is US Postal Service ZIP Code validation. Two ZIP Code formats are allowed: the old 5-digit format and the ZIP+4 format that we started using the 1980’s (5-digits, followed by a dash, followed by four more digits).

Using what you learned in my first post of the series you can probably easily write a formula to validate each one of those. Instead of walking through each step of writing those, I’ll just write them for you here:

ZIP Code Validation Formula:

=(LEN([ZIP Code])=5)
+(CODE(MID([ZIP Code],1,1))>47)
+(CODE(MID([ZIP Code],1,1))<58)
+(CODE(MID([ZIP Code],2,1))>47)
+(CODE(MID([ZIP Code],2,1))<58)
+(CODE(MID([ZIP Code],3,1))>47)
+(CODE(MID([ZIP Code],3,1))<58)
+(CODE(MID([ZIP Code],4,1))>47)
+(CODE(MID([ZIP Code],4,1))<58)
+(CODE(MID([ZIP Code],5,1))>47)
+(CODE(MID([ZIP Code],5,1))<58)
=11

ZIP+4 Validation Formula:

=(LEN([ZIP Code])=10)
+(CODE(MID([ZIP Code],1,1))>47)
+(CODE(MID([ZIP Code],1,1))<58)
+(CODE(MID([ZIP Code],2,1))>47)
+(CODE(MID([ZIP Code],2,1))<58)
+(CODE(MID([ZIP Code],3,1))>47)
+(CODE(MID([ZIP Code],3,1))<58)
+(CODE(MID([ZIP Code],4,1))>47)
+(CODE(MID([ZIP Code],4,1))<58)
+(CODE(MID([ZIP Code],5,1))>47)
+(CODE(MID([ZIP Code],5,1))<58)
+(MID([ZIP Code],6,1)="-")
+(CODE(MID([ZIP Code],7,1))>47)
+(CODE(MID([ZIP Code],7,1))<58)
+(CODE(MID([ZIP Code],8,1))>47)
+(CODE(MID([ZIP Code],8,1))<58)
+(CODE(MID([ZIP Code],9,1))>47)
+(CODE(MID([ZIP Code],9,1))<58)
+(CODE(MID([ZIP Code],10,1))>47)
+(CODE(MID([ZIP Code],10,1))<58)
=20

Set Up a Sandbox List for Experimenting

Since I’m using these blog posts to teach how to do column validation formatting instead of just giving you the formulas without much explanation, let’s create a sandbox list we can play in and see what is going on. We’ll need 3 columns in the list: a Single line of text for the ZIP Code, a calculated field that uses the first formula for the ZIP Formula, and another calculated field that uses the ZIP+4 Formula. I’ve set that up here and entered a couple examples:

Because those formulas are so long, we’ll write our initial formula using the ZIP Formula column and ZIP=4 Formula column. Then we’ll replace those with the full formulas.

The Logic – Introducing the Undocumented OR() Formula

Let’s create a new calculated column in our sandbox list and call it Combined Formula. Although I found information about the AND() formula, I didn’t see any documentation about an OR() function. I decided to try it and see if it would work anyway. It did! At least, part way. Enter in a formula of =OR([ZIP Formula],[ZIP+4 Formula]). You’ll notice you get the following results:

Dealing with Errors

See those #VALUE! Errors? Ultimately, we know those are No’s, but SharePoint sees them as errors. When a validation formula encounters an error it will give the user an error instead of the message you want. Here is a screenshot showing what the user sees when a validation formula encounters an unchecked error.

What we wanted them to see was this:

We can handle this with the ISERROR() formula we used in yesterday’s post. We’ll also combine it with the IF() function I mentioned briefly in the first post. The IF() function accepts three comma separated parameters: the item to test, the result if the test is true, and the result if the test is false.

To see this in action, let’s work with one column at a time in our Combined Column. Change the formula for this column to =IF(ISERROR([ZIP Formula]),FALSE,IF([ZIP Formula],TRUE,FALSE)). What this does is test for an error first. If it encounters an error then it sets the field to FALSE, if it doesn’t encounter an error, then it checks to see if the formula evaluates to True or False and sets the value accordingly.

This gives us the accurate Yes’s and No’s about the ZIP Formula, without the errors:

Now that we have that working with the ZIP Formula column, let’s modify it to use the ZIP+4 Formula column. That’s as simple as changing the column names: =IF(ISERROR([ZIP+4 Formula]),FALSE,IF([ZIP+4 Formula],TRUE,FALSE)).

Notice this also correctly tells us that the last test is the only one that fits our ZIP+4 criteria:

Back to the Logic

Now that we have the errors all worked out, let’s use the OR() function to see if either of them are valid. Here is what the formula will look like: =OR((IF(ISERROR([ZIP Formula]),FALSE,IF([ZIP Formula],TRUE,FALSE))),(IF(ISERROR([ZIP+4 Formula]),FALSE,IF([ZIP+4 Formula],TRUE,FALSE)))). Notice that this shows us that either of the last two entries are valid, but neither of the first two are:

Putting It All Together

Chances are pretty good that you don’t want to add two extra columns to your SharePoint list every time you have a ZIP Code column for validation purposes. So we’ll need to put our original formulas in the place of the column names we used in our last formula. If you’re thinking, “This is going to be one really long formula!” then you’re absolutely correct!

To make things easier, let’s take that last formula and break it out on different lines and add some indentation so it’s easier to read:

=OR(
  (
    IF(ISERROR(
        [ZIP Formula]
      )
      ,FALSE
      ,IF(
        [ZIP Formula]
        ,TRUE
        ,FALSE
       )
      ))
    ,(IF(ISERROR(
        [ZIP+4 Formula]
      )
      ,FALSE
      ,IF(
         [ZIP+4 Formula]
         ,TRUE
         ,FALSE
    ))
  )
)

Now that you have that, simple replace both instances of [ZIP Formula] with the first formula from the beginning of this post and both instances of the [ZIP=4 Formula] with the second formula (remember to drop the equals sign at the beginning and wrap it in parenthesis). This will give you a completed formula that looks like this:

=OR(
  (
    IF(ISERROR(
        ((LEN([ZIP Code])=5)
        +(CODE(MID([ZIP Code],1,1))>47)
        +(CODE(MID([ZIP Code],1,1))<58)
        +(CODE(MID([ZIP Code],2,1))>47)
        +(CODE(MID([ZIP Code],2,1))<58)
        +(CODE(MID([ZIP Code],3,1))>47)
        +(CODE(MID([ZIP Code],3,1))<58)
        +(CODE(MID([ZIP Code],4,1))>47)
        +(CODE(MID([ZIP Code],4,1))<58)
        +(CODE(MID([ZIP Code],5,1))>47)
        +(CODE(MID([ZIP Code],5,1))<58)
        =11)
      )
      ,FALSE
      ,IF(
        ((LEN([ZIP Code])=5)
        +(CODE(MID([ZIP Code],1,1))>47)
        +(CODE(MID([ZIP Code],1,1))<58)
        +(CODE(MID([ZIP Code],2,1))>47)
        +(CODE(MID([ZIP Code],2,1))<58)
        +(CODE(MID([ZIP Code],3,1))>47)
        +(CODE(MID([ZIP Code],3,1))<58)
        +(CODE(MID([ZIP Code],4,1))>47)
        +(CODE(MID([ZIP Code],4,1))<58)
        +(CODE(MID([ZIP Code],5,1))>47)
        +(CODE(MID([ZIP Code],5,1))<58)
        =11)
        ,TRUE
        ,FALSE
       )
      ))
    ,(IF(ISERROR(
        ((LEN([ZIP Code])=10)
        +(CODE(MID([ZIP Code],1,1))>47)
        +(CODE(MID([ZIP Code],1,1))<58)
        +(CODE(MID([ZIP Code],2,1))>47)
        +(CODE(MID([ZIP Code],2,1))<58)
        +(CODE(MID([ZIP Code],3,1))>47)
        +(CODE(MID([ZIP Code],3,1))<58)
        +(CODE(MID([ZIP Code],4,1))>47)
        +(CODE(MID([ZIP Code],4,1))<58)
        +(CODE(MID([ZIP Code],5,1))>47)
        +(CODE(MID([ZIP Code],5,1))<58)
        +(MID([ZIP Code],6,1)="-")
        +(CODE(MID([ZIP Code],7,1))>47)
        +(CODE(MID([ZIP Code],7,1))<58)
        +(CODE(MID([ZIP Code],8,1))>47)
        +(CODE(MID([ZIP Code],8,1))<58)
        +(CODE(MID([ZIP Code],9,1))>47)
        +(CODE(MID([ZIP Code],9,1))<58)
        +(CODE(MID([ZIP Code],10,1))>47)
        +(CODE(MID([ZIP Code],10,1))<58)
        =20)
      )
      ,FALSE
      ,IF(
        ((LEN([ZIP Code])=10)
        +(CODE(MID([ZIP Code],1,1))>47)
        +(CODE(MID([ZIP Code],1,1))<58)
        +(CODE(MID([ZIP Code],2,1))>47)
        +(CODE(MID([ZIP Code],2,1))<58)
        +(CODE(MID([ZIP Code],3,1))>47)
        +(CODE(MID([ZIP Code],3,1))<58)
        +(CODE(MID([ZIP Code],4,1))>47)
        +(CODE(MID([ZIP Code],4,1))<58)
        +(CODE(MID([ZIP Code],5,1))>47)
        +(CODE(MID([ZIP Code],5,1))<58)
        +(MID([ZIP Code],6,1)="-")
        +(CODE(MID([ZIP Code],7,1))>47)
        +(CODE(MID([ZIP Code],7,1))<58)
        +(CODE(MID([ZIP Code],8,1))>47)
        +(CODE(MID([ZIP Code],8,1))<58)
        +(CODE(MID([ZIP Code],9,1))>47)
        +(CODE(MID([ZIP Code],9,1))<58)
        +(CODE(MID([ZIP Code],10,1))>47)
        +(CODE(MID([ZIP Code],10,1))<58)
        =20)
         ,TRUE
         ,FALSE
    ))
  )
)

Whew! Now that’s what I call validation!

Before we add this to our validation field, let’s test the combined formula in our Combined Formula calculated column. As you can see, this gives us the same result we got before:

1024 Character Limit for Column Validation Formulas

If you paste this formula into the Column Validation field on the ZIP Code column, you’ll notice that it trims it about half-way. Microsoft is enforcing a 1,024-character limit on Column Validation formulas and this formula has 2,815-characters with the indented spacing. If we remove all the extra spaces, that brings it down to 2,135-characters, bit that’s still 1,111 too many. If we change our column name from ZIP Code to simply ZIP, we can save 7-characters each time we reference it (we don’t have to use the square brackets for single word column names). This brings us down to 1,704-characters. We still need to eliminate 680 characters. Can we do it?

The first formula is now 279-characters and the second formula is now 511-characters. If we can reference each formula only once, that will save 790-characters and get us under our limit. Hmmm…

Rephrasing the Solution

As it turns out, there is a way to only reference each formula one time. We only need to use the ZIP formula if the user entered 5-characters and we only need to use the ZIP+4 formula if the user entered 10-characters. Anything except 5- or 10-characters is not valid, so we don’t need to test that. This also means that we don’t need to do any error checking.

So, let’s begin with an IF() statement this time that just tests to see if the input is 5-characters long: =IF(LEN(ZIP)=5,TRUE,FALSE).

Then we’ll add in the test for 10-characters long if it fails the 5-character test: =IF(LEN(ZIP)=5,TRUE,(IF(LEN(ZIP)=10,TRUE,FALSE))).

Let’s write this with some indentation so we can see what we’re working with. We can always replace the spaces if we need more characters.

=IF(
  LEN(ZIP)=5
    ,TRUE
    ,(IF(
      LEN(ZIP)=10
        ,TRUE
        ,FALSE
    ))
)

Now, all we need to do is replace the TRUE portions of this formula with the tests we want to run. We can also exclude the length test now too. So, here is what the formula looks like:

=IF(
  LEN(ZIP)=5
    ,( (CODE(MID(ZIP,1,1))>47)
      +(CODE(MID(ZIP,1,1))<58)
      +(CODE(MID(ZIP,2,1))>47)
      +(CODE(MID(ZIP,2,1))<58)
      +(CODE(MID(ZIP,3,1))>47)
      +(CODE(MID(ZIP,3,1))<58)
      +(CODE(MID(ZIP,4,1))>47)
      +(CODE(MID(ZIP,4,1))<58)
      +(CODE(MID(ZIP,5,1))>47)
      +(CODE(MID(ZIP,5,1))<58)
      =10)
    ,(IF(
      LEN(ZIP)=10
        ,((CODE(MID(ZIP,1,1))>47)
        +(CODE(MID(ZIP,1,1))<58)
        +(CODE(MID(ZIP,2,1))>47)
        +(CODE(MID(ZIP,2,1))<58)
        +(CODE(MID(ZIP,3,1))>47)
        +(CODE(MID(ZIP,3,1))<58)
        +(CODE(MID(ZIP,4,1))>47)
        +(CODE(MID(ZIP,4,1))<58)
        +(CODE(MID(ZIP,5,1))>47)
        +(CODE(MID(ZIP,5,1))<58)
        +(MID(ZIP,6,1)="-")
        +(CODE(MID(ZIP,7,1))>47)
        +(CODE(MID(ZIP,7,1))<58)
        +(CODE(MID(ZIP,8,1))>47)
        +(CODE(MID(ZIP,8,1))<58)
        +(CODE(MID(ZIP,9,1))>47)
        +(CODE(MID(ZIP,9,1))<58)
        +(CODE(MID(ZIP,10,1))>47)
        +(CODE(MID(ZIP,10,1))<58)
        =19)
        ,FALSE
    ))
)

That’s 1088-characters with the spaces for indentation—816 if we remove all the spaces. We did it! In fact, it could probably made even smaller by checking the first five characters separately from the last 5. I’ll leave that as a homework exercise for you though.

Implementing the Solution

Now let’s try out the formula, add a friendly validation message, and try to enter some valid and invalid Zip Codes.

Notice that anything that doesn’t meet our strict criteria displays our friendly error message:

What Have We Learned?

I’ve been all over the place in this post, but I think you’ve learned a lot. Let me summarize:

• It is possible to have multiple options that are valid (‘OR’ type options).
• This can be accomplished through IF() functions and/or the OR() function.
• Validation formulas that result in an error, rather than TRUE or FALSE, will show an unfriendly error message instead of the friendly error the user entered.
• ISERROR() can be used to trap errors if they may be encountered.
• There is a 1024-character limit to Column Validation formulas.
• Columns with a single name don’t need square brackets around them in formulas.
• The most obvious formulas can often be shortened by looking at the problem a different way.

Is there anything I haven’t touched on in these three posts? What other kinds of validation have you not been able to do? Let me know in the comments!

Yesterday I showed you how to use Column Validation to validate Social Security Numbers, Phone Numbers, Employee Numbers, Part Numbers, and other things with strict formatting. Today I’m going to expand on that idea by showing you something more complex—we’re going to validate that an acceptable email address has been entered.

The formula we’ll write in this post won’t catch every possible bad email address, but it’s designed to catch the most common mistakes. I’ll begin with the entire formula, then I’ll break it down and explain what’s going on piece by piece. This formula checks to see that the email address contains some characters, followed by an ampersand, more characters, a dot, and more characters—it also checks to make sure it doesn’t include a space character. Here is the formula:

=(LEN(LEFT([Email],FIND("@",[Email])-1))>0)
+(LEN(RIGHT([Email],LEN([Email])-FIND(".",[Email],FIND("@",[Email]))))>0)
+(LEN(MID([Email],FIND("@",[Email])+1,FIND(".",[Email],FIND("@",[Email]))-FIND("@",[Email])-1))>0)
+(ISERROR(FIND(" ",[Email]))=TRUE)
=4

Whew! What a formula. Let’s break it down…

Finding the Ampersand

The first thing we want to do is find the position of the ampersand in the string the user enters. I’ve created a custom list to work with. I’ve added a column named Email (of type Single line of text) that will be where the user enters the e-mail address and I’ve added a calculated column named Ampersand to store the position of the ampersand (we won’t need this column for the validation, but it will help you understand how this formula works). We’ll use the FIND function to get this character location (Click here for details about all the formulas and functions that are available to you for column validation). The formula for the Ampersand column is =FIND(“@”,[Email]).

If we enter in an email address of test@testing.com, you’ll notice this column correctly populates with 5.

Find the Characters Before the Ampersand

You’ll remember that we need to make sure the ampersand isn’t the first character in the email address. So the next thing we want to do is get all the text that is before the email address. To do that we’ll add a calculated column named First and use the formula =LEFT([Email],FIND("@",[Email])).

This results in “test@”:

We don’t need the @ sign on the end of it, but the LEFT() function gives us everything up to and including the position we give it. So we’ll need to subtract one from it for it to be correct. Let’s change it to =LEFT([Email],FIND("@",[Email])-1). This gives us what we want.

Now that we have that, we can see how many characters long it is by using the LEN() function. Let’s add another column named FirstLen that uses that function around all this, like so: =LEN(LEFT([Email],FIND("@",[Email])-1)).

You’ll notice this column contains 4 for the original example and it contains 0 if we enter in an email address of just “@.”

We know that this length must be greater than zero to validate, so, with that we now we have the first part of our column validation for the Email column:
=(LEN(LEFT([Email],FIND("@",[Email])-1))>0)

If we enter this and try to add an email address that begins with an ampersand, or doesn’t contain an ampersand at all, we’ll get an error.

Find the Characters After the Dot

You’ve probably already figured out that you can find the location of the dot by using the formula  =FIND(".",[Email]). However, if someone enters an email address with a dot before the ampersand (such as testy.tester@testing.com) this will cause a problem for our formula. We need to find a dot that is after the ampersand. We can modify the FIND() formula to start looking where the ampersand is though by changing it to: =FIND(".",[Email],FIND("@",[Email])).

Let’s create a calculated column called dot with this formula. Notice this correctly shows us the position of a dot that is after the ampersand.

We’re almost ready to use the RIGHT() function to extract the characters we need. Let’s create a calculated column named Right and use the formula: =RIGHT([Email],FIND(".",[Email],FIND("@",[Email])))

You’ll notice this gives us far more than just the few characters we want:

This is because the RIGHT() function begins with the character we give it from the left, not from the right. So we need to translate that to characters from the right. We can do that by simply subtracting that number from the length of the whole string using the LEN() function. Let’s modify the formula like this: =RIGHT([Email],LEN([Email])-FIND(".",[Email],FIND("@",[Email]))

This gives us what we want.

Now we just need to get the length of that piece. We’ll create a new calculated column called RightLen and use the formula: =LEN(RIGHT([Email],LEN([Email])-FIND(".",[Email],FIND("@",[Email]))))

Since this is working correctly, we can add that to our validation rule on the Email column to check to make sure this value is greater than zero. Here is the Email Validation formula so far:

=(LEN(LEFT([Email],FIND("@",[Email])-1))>0)
+(LEN(RIGHT([Email],LEN([Email])-FIND(".",[Email],FIND("@",[Email]))))>0)
=2

If we try to enter an email address without a top level domain on the end, we get the error.

Find the Characters Between the Ampersand and the Dot

We can’t have a valid email address without a domain name between the ampersand and the dot. So now we need to figure out how to check for that. For this we’ll use the MID() function and all the skills you’ve learned up to this point. Create a new calculated column named Middle and use the formula: =MID([Email],FIND("@",[Email])+1,FIND(".",[Email],FIND("@",[Email]))-FIND("@",[Email])-1)

This correctly gives us the domain name of the email address:

We’ll create a calculated column named MiddleLen and wrap the formula in a LEN() function to give the length of this part, like so: =LEN(MID([Email],FIND("@",[Email])+1,FIND(".",[Email],FIND("@",[Email]))-FIND("@",[Email])-1))

This correctly gives us:

We now have the bulk of our email validation formula done. We just need it to check if the length of all three parts is greater than zero. Here is the Email Validation formula so far:

=(LEN(LEFT([Email],FIND("@",[Email])-1))>0)
+(LEN(RIGHT([Email],LEN([Email])-FIND(".",[Email],FIND("@",[Email]))))>0)
+(LEN(MID([Email],FIND("@",[Email])+1,FIND(".",[Email],FIND("@",[Email]))-FIND("@",[Email])-1))>0)
=3

If we try to use this and leave out the domain portion, we’ll get an error.

Check for Spaces in the Email Address

As a final addition to our formula, we’re going to check for a space in the email address. We know that it’s easy to accidentally press the space-bar while typing, but spaces aren’t allowed.

Let’s add a new calculated column named SpacePos and use the following formula to find the position of any space: =FIND(" ",[Email])

Notice the column contains a #VALUE error if the Email address doesn’t contain a space and the position of the space character if it does.

There is another function called ISERROR() that allows us to check for these kinds of errors. Change the formula on the SpacePos column to: ISERROR(FIND(" ",[Email]))

Notice that the ones that don’t contain a space evaluate to TRUE and the one that does evaluates to FALSE:

We can now use this as a fourth piece of criteria to check in our formula by adding it to our email validation formula. Here is the complete formula:

=(LEN(LEFT([Email],FIND("@",[Email])-1))>0)
+(LEN(RIGHT([Email],LEN([Email])-FIND(".",[Email],FIND("@",[Email]))))>0)
+(LEN(MID([Email],FIND("@",[Email])+1,FIND(".",[Email],FIND("@",[Email]))-FIND("@",[Email])-1))>0)
+(ISERROR(FIND(" ",[Email]))=TRUE)
=4

More…

I hope this has been helpful and inspires you to do more serious data validation. If it does, please share any helpful formulas you discover in the comments. If you have things that you just can’t seem to figure out how to validate, share those as well and perhaps someone else can help with those. Almost anything you need to validate is possible with a little work.

We often need to ensure that users enter text information in a specific way. For example, perhaps our company uses part numbers such as TL-1400 and RW-1555. Every part number begins with two letters, followed by a dash, followed by four digits. Or perhaps we use Social Security Numbers in SharePoint (if you do, I’m sure you have the appropriate security in place) and need to insure they always follow the same format of three digits, a dash, two digits, a dash, and four digits. We may want to make sure that phone numbers always follow the format of three digits, a dash, three digits, a dash, and four digits. We may want to make sure that an email address field contains some characters, an ampersand, some more characters, a dot, and some more characters (as well as doesn’t include spaces and other special characters).

In this post, I’m going to explain how to validate these types of information and put you on the path to validating your own text formatting in your organization. This is a great method to use when creating a Site Column for frequently used information that needs strict formatting.

Several people have written about how to use SharePoint Designer to modify forms to do this type of validation but that is only a solution for a single instance of usage. If you’re running Enterprise Edition, you can modify your list forms in InfoPath and do formatting validation there. However, once again, it only solves the problem for that particular list.

Both the SharePoint Designer and InfoPath solutions use Regular Expressions. This is a very easy way to validate information (although it’s not so easy to learn). Although SharePoint 2010 does allow Column Validation, Regular Expressions are not supported. However, there are lots of string functions we can use to meet our needs!  Click here to access information about the formulas that are available in SharePoint 2010 and how to use them.

Validate a Social Security Number in SharePoint 2010

Let’s begin by seeing how we might validate a Social Security Number in SharePoint 2010. I’ve created a custom list and added a column of data type Single line of text named SSN.

Our requirements are that the field must be 11 characters long—consisting of three digits, a dash, two digits, a dash, and four digits. Let’s edit the column and create a formula to check only the number of characters. We’ll use the formula =Len([SSN])=11and also set our User message we want people to see when we have everything working to, “Must be in the format ###-##-####!“

If we test this by trying to add a list item that has only 10 characters, we see our error message.

So, we have the first piece of criteria we need. We’ll remember that and work on another piece. Let’s validate that the fourth character is a dash. To do this, we’ll use the MID function like so: =MID([SSN],4,1=”-“). This tells SharePoint to extract a string out of the MIDdle of our field that is 1 character long and begins with the character in the 4th position, then check to see if it is a dash.

If we try to enter any value that doesn’t have a dash as the fourth character, we see the error.

However, it accepts anything that has a dash in that position.

The next thing we want to do is combine these two formulas. You would think we could use the AND operator like so: =LEN([SSN])=11 AND (MID(SSN,4,1)="-"). However SharePOint doesn’t like that and will tell us “The formula contains a syntax error or is not supported.” Ugh! We could use the IF function, but it creates a mess that looks like this: =IF(LEN([SSN])=11,(IF(MID([SSN],4,1)="-",TRUE,FALSE)),FALSE). Yuch! In fact, double Yuch! If you think that’s difficult to read (not to mention write and debug) with only two pieces of criteria, wait until we have 21 pieces of criteria! That’s just not usable at all. So, is there anything else we can do? Yes there is!

A TRUE value equates to the number 1 while a FALSE value equates to 0. We can use this fact to create a formula that is easier to read, like so:
=(LEN([SSN])=11)
+(MID([SSN],4,1)="-")
=2

This tells SharePoint to take the result of the first condition, add it to the result of the second condition and see if the combined result is 2. If it is, our formula will evaluate to TRUE.

With this formula, both pieces of criteria must be met for SharePoint to allow the entry:

Now that we have a simple way to check for multiple conditions, we can add a check for the second dash in the seventh position:
=(LEN([SSN])=11)
+(MID([SSN],4,1)="-")
+(MID([SSN],7,1)="-")
=3

The next thing we want to do is make sure that the first character is a digit. To do this, we’ll look at it’s ASCII value. Click here for a chart you can use to look up ASCII values. The number 0 has an ASCII value of 48 and the number 9 has an ASCII value of 57. So we can do two checks to see if the value is greater than 47 and less than 58. We’ll use the CODE function to get the value. If we add these two tests to our function, it will look like this:
=(LEN([SSN])=11)
+(MID([SSN],4,1)="-")
+(MID([SSN],7,1)="-")
+(CODE(MID([SSN],1,1))>47)
+(CODE(MID([SSN],1,1))<58)
=5

Now, our field must be eleven characters long, have a dash as the fourth character, have a dash as the seventh character, and must begin with a number.

Now you know everything you need to know to finish the formula to have it check for digits in the other eight positions. Here is the finished formula:
=(LEN([SSN])=11)
+(MID([SSN],4,1)="-")
+(MID([SSN],7,1)="-")
+(CODE(MID([SSN],1,1))>47)
+(CODE(MID([SSN],1,1))<58)
+(CODE(MID([SSN],2,1))>47)
+(CODE(MID([SSN],2,1))<58)
+(CODE(MID([SSN],3,1))>47)
+(CODE(MID([SSN],3,1))<58)
+(CODE(MID([SSN],5,1))>47)
+(CODE(MID([SSN],5,1))<58)
+(CODE(MID([SSN],5,1))>47)
+(CODE(MID([SSN],5,1))<58)
+(CODE(MID([SSN],6,1))>47)
+(CODE(MID([SSN],6,1))<58)
+(CODE(MID([SSN],6,1))>47)
+(CODE(MID([SSN],6,1))<58)
+(CODE(MID([SSN],6,1))>47)
+(CODE(MID([SSN],6,1))<58)
+(CODE(MID([SSN],6,1))>47)
+(CODE(MID([SSN],6,1))<58)
=21

Validate a Telephone Number in SharePoint 2010

We can use these these same techniques to validate a phone number. Here is a formula to validate for ###-###-#### format:
=(LEN([Phone])=12)
+(CODE(MID([Phone],1,1))>47)
+(CODE(MID([Phone],1,1))<58)
+(CODE(MID([Phone],2,1))>47)
+(CODE(MID([Phone],2,1))<58)
+(CODE(MID([Phone],3,1))>47)
+(CODE(MID([Phone],3,1))<58)
+(MID([Phone],4,1)="-")
+(CODE(MID([Phone],5,1))>47)
+(CODE(MID([Phone],5,1))<58)
+(CODE(MID([Phone],6,1))>47)
+(CODE(MID([Phone],6,1))<58)
+(CODE(MID([Phone],7,1))>47)
+(CODE(MID([Phone],7,1))<58)
+(MID([Phone],8,1)="-")
+(CODE(MID([Phone],9,1))>47)
+(CODE(MID([Phone],9,1))<58)
+(CODE(MID([Phone],10,1))>47)
+(CODE(MID([Phone],10,1))<58)
+(CODE(MID([Phone],11,1))>47)
+(CODE(MID([Phone],11,1))<58)
+(CODE(MID([Phone],12,1))>47)
+(CODE(MID([Phone],12,1))<58)
=23

And here is a formula to validate for (###)###-#### format:
=(LEN([Phone])=13)
+(MID([Phone],1,1)="(")
+(CODE(MID([Phone],2,1))>47)
+(CODE(MID([Phone],2,1))<58)
+(CODE(MID([Phone],3,1))>47)
+(CODE(MID([Phone],3,1))<58)
+(CODE(MID([Phone],4,1))>47)
+(CODE(MID([Phone],4,1))<58)
+(MID([Phone],5,1)=")")
+(CODE(MID([Phone],6,1))>47)
+(CODE(MID([Phone],6,1))<58)
+(CODE(MID([Phone],7,1))>47)
+(CODE(MID([Phone],7,1))<58)
+(CODE(MID([Phone],8,1))>47)
+(CODE(MID([Phone],8,1))<58)
+(MID([Phone],9,1)="-")
+(CODE(MID([Phone],10,1))>47)
+(CODE(MID([Phone],10,1))<58)
+(CODE(MID([Phone],11,1))>47)
+(CODE(MID([Phone],11,1))<58)
+(CODE(MID([Phone],12,1))>47)
+(CODE(MID([Phone],12,1))<58)
+(CODE(MID([Phone],13,1))>47)
+(CODE(MID([Phone],13,1))<58)
=24

Validate a Part Number in SharePoint 2010

In my opening paragraph I mentioned the possibility of a part number in the format of two letters, a dash, and four numbers such as TL-1400 or RW-1555. We could validate this with:
=(LEN([PartNum])=7)
+(CODE(MID([PartNum],2,1))>64)
+(CODE(MID([PartNum],2,1))<90)
+(CODE(MID([PartNum],3,1))>64)
+(CODE(MID([PartNum],3,1))<90)
+(MID([PartNum],4,1)="-")
+(CODE(MID([PartNum],5,1))>47)
+(CODE(MID([PartNum],5,1))<58)
+(CODE(MID([PartNum],6,1))>47)
+(CODE(MID([PartNum],6,1))<58)
+(CODE(MID([PartNum],7,1))>47)
+(CODE(MID([PartNum],7,1))<58)
+(CODE(MID([PartNum],8,1))>47)
+(CODE(MID([PartNum],8,1))<58)
=14

More…

I hope this has been helpful. Feel free to leave helpful formulas you’ve developed in the comments!

If you have ever used a SharePoint list containing lookup columns as a data source in your InfoPath forms, you may have been disappointed to see the value of the lookup is not displayed. Instead of the value, the list ID of the item containing the value is displayed.

My ‘Construction Line Items’ list contains three lookup columns: Project, Category, and Product.

When I connect to this list from InfoPath, my repeating table shows the lookup item IDs and not the values like I want.

This example uses the following four SharePoint lists: Line Items, Projects, Construction Category, Products and Activities.

The InfoPath form connects to the Line Items list and displays the data in a Repeating Table

So, how can you get InfoPath to display the lookup column value? One way is to add a data connection to the list (or lists) containing the lookup values and change the repeating table field controls from text boxes to calculated fields. Here are the steps:

1. Create data connections to the lists containing the lookup values

   

2. Change the repeating table field control from Text Box to Calculated Value
   
   
3. Edit Formula on the calculated field control (Right-click, Calculated Value Properties or double-click, Control Properties)

   

4. Delete the current formula and Insert Field or Group > change the Fields source to the data connection containing the lookup value (Construction Category) > select the lookup field column (Category) > click Filter Data
   
   
5. Set the filter where the ID (on the Construction Category) > is equal to > Select a field or group > change fields to the Line Items data connection > select the corresponding column (Category on the repeating table connection) > OK

   

6. Preview the form to see that the categories are now showing instead of the lookup IDs

   

7. Following the same steps for the Item column allows the values to show there too instead of the lookup item IDs.

   

Let me know if you have another way of accomplishing the same thing.

I just finished upgrading a three-server SharePoint 2010 farm (1 Web Front End (WFE01), 1 Application Server (APP01), and a separate Database Server; almost exactly as described in Jeff’s post How to Scale Out a SharePoint 2010 Farm from Two-Tier to Three-Tier By Adding a Dedicated Application Server) to Service Pack 1 and the June 2011 Cumulative Update. This isn’t going to be an exhaustive post that digs into the details of how to do this. I’m mostly just sharing a few things I either found interesting or would have liked to have known beforehand. Feel free to post your own comments below too.

Backup First!

Hopefully you will have the luxury of first installing the updates in an a test environment that is identical to your production environment. Unfortunately, I expect that isn’t going to be the case for most people. At least make sure that you have backups of both your databases and the servers. If you’re in a Hyper-V environment, make snapshots of the servers so you can revert if you have to.

Downloads

I recommend that you download EVERYTHING you’ll need before you begin installing anything. You would be in a pickle if you ended up not being able to download one of the necessary files in the middle of the process. You’ll have to request that Microsoft email you links to the Cumulative Updates—these aren’t available for public download otherwise. The CUs are executable zip files and they mostly contain only a single EXE.

The Install Order

Although there have been several posts both from Microsoft and other sources about the proper order to install things, there seems to be a lot of disagreement. This client was running SharePoint Server 2010 Enterprise Edition and was also running the Office Web Applications. Here are the general steps I followed and a few things I found notable.

1. Install SharePoint Foundation 2010 SP1 on WFE01. (sharepointfoundation2010sp1-kb2460058-x64-fullfile-en-us.exe) This installed quickly in about 4-minutes. No reboot was required afterward.
2. Install SharePoint Foundation 2010 SP1 on APP01. Another quick installation. A reboot was required, but the Application Server is running different services than the WFE, so this was not abnormal.
3. Install SharePoint Server 2010 SP1 on WFE01. (officeserver2010sp1-kb2460045-x64-fullfile-en-us.exe) Took about 15-minutes to install.
4. Install SharePoint Server 2010 SP1 on APP01. Took about 15-minutes to install and a reboot was required afterward.
5. Run Configuration Wizard on WFE01. This took about 15-minutes. I go an error at the end, and then realized I was not logged in as the Farm account, so I logged back in with the SP_Farm account (that’s the account I usually use for setups). The second time it completed successfully in about 3-minutes.
6. Run Configuration Wizard on APP01. Ran in about 5-minutes with no problems (logged in as SP_Farm this time though).
7. Install Office Web Apps SP1 on WFE01. (wacserver2010sp1-kb2460073-x64-fullfile-en-us.exe) The install requests to stop and restart the SharePoint 2010 Timer Application. I allowed this so a reboot wouldn’t be required. It took less than 3-minutes to install.
8. Install Office Web Apps SP1 on APP01. Installed in about 3-minutes.
9. Run Configuration Wizard on WFE01. Ran in about 3-minutes without errors.
10. Run Configuration Wizard on APP01. Ran in about 3-minutes without errors.
11. Install June CU for SharePoint on WFE01. (435088_intl_x64_zip.exe) Took about 17-minute to install. I found it interesting that this file was nearly 1Gb; almost three times as large as SP1!
12. Install June CU for SharePoint on APP01. Took about 18-minutes to install.
13. Run Configuration Wizard on WFE01. Took about 7-minutes to run and I got an error at the end. Although I wasn’t prompted to reboot, it seems that a reboot is required after installing this. The Wizard ran in about 2-minutes after this.
14. Run Configuration Wizard on APP01. I learned my lesson from before and rebooted before running this. It ran in about 4-minutes with no problems.
15. Install June CU for Office Web Apps on WFE01. (434726_intl_x64_zip.exe) The install requests to stop and restart the SharePoint 2010 Timer Application. I allowed this so a reboot wouldn’t be required. It took less than 3-minutes to install.
16. Install June CU for Office Web Apps on APP01. Process was exactly as previous step.
17. Run Configuration Wizard on WFE01. Took about 7-minutes to run and was successful.
18. Run Configuration Wizard on APP01. Took about 2-minutes to run and was successful.

A Coworker pointed out to me that I had overlooked installing the June 2011 Cumulative Update for SharePoint Foundation 2010. If I had to do it over, the following four steps would have been after step 10 above and before step 11.

1. Install June CU for SharePoint Foundation 2010 on WFE01. (435083_intl_x64_zip.exe) When I tried to install this, it told me, “There are no products affected by this package installed on this system.” This indicated to me that everything in the Foundation 2010 CU was actually in the Server CU. So maybe this didn’t need to be installed afterall.
2. Install June CU for Office Web Apps on APP01. See previous notes—I was able to skip this.
3. Run Configuration Wizard on WFE01. No need for this since I didn’t install the CU.
4. Run Configuration Wizard on APP01. No need for this since I didn’t install the CU.

Testing! Testing! Testing!

Regardless of whether you’re doing this in a production environment or a testing/development environment, you’ll want to test everything you can think of such as:

• Edit and View Word, Excel, PowerPoint, and OneNote documents in the browser.
• View My Sites.
• Test User Profile Synchronization. – After the updates, the User Profile Synchronization Service was stopped. I started it on the Application Server and all was well. One thing I noticed was that an incremental profile synchronization used to take 10- to 15-minutes to complete (it contained both import and export items). After the update it took less than 45-minutes.
• Tag Pages and check for tags in Newsfeed. – One of the problems the client had before the update was that Tag Profile pages didn’t display the All history (only the last sixty-days). After the update, Tag Profile pages were working properly.
• View a Visio Web Access Drawing in the Browser.
• Work in an Access Web Database site.
• Add an obscure word to a page or document, do an incremental search crawl, and search for the word.
• Add terms and/or term sets to the Managed Metadata store.
• View Reporting Services Reports (or Access Web Database Reports).

One of the SharePoint Designer workflow activities I use most in SharePoint 2007 is Build Dynamic String. It is a very convenient way to create a customized string of dynamic text that I can then use and re-use for email subject lines, descriptions, task titles, and all sorts of things.

Unfortunately, Microsoft made the decision to disable the Build Dynamic String workflow activity in SharePoint 2010. You may have already discovered Greg Osimowicz’s blog post about how to re-enable the Build Dynamic String activity in SharePoint 2010. However, not only is this method neither recommended or supported by Microsoft, but if you aren’t the SharePoint Server administrator, you probably can’t edit the WSS.Actions file anyway. So, how can YOU, as a workflow designer, Build a Dynamic String in a workflow in SharePoint Designer 2010?

In SharePoint 2010, Microsoft has replaced the Build Dynamic String activity with four new Utility Actions to Extract Substrings. While all of them are useful, only one of them properly replicates the Build Dynamic String activity from SharePoint 2007: Extract Substring from Index of String. In this post I’ll explain how to use this and then explain why the other three don’t properly duplicate the Build Dynamic String function.

How to Use Extract Substring from Index of String Workflow Activity

When you add the Extract Substring from Index of String activity to your workflow, it will look like this:

You’ll notice that there are three parameters you can edit: the original string, the starting character, and a variable to store the result. If you change the starting character from 0 to 1, this activity will work exactly like the Build Dynamic String activity did in SharePoint Designer 2007. Here, you can see how the parameters line up:

When you click on string, you will have the option of clicking on an ellipses button […] to Display builder for this parameter, or the [fx] button to Define workflow lookup. If you click on the ellipses, you will see the String Builder window just like you were used to in SharePoint 2007. Use this window to build your dynamic string as you want it. Then change the Output Variable to a variable of your choosing. That’s all there is to it! This

Something to Be Aware of – In order for this to work, you must change the starting at value to 1. If you leave it at 0, you will end up with an empty string. If you use a value greater than 1 your final string will be missing characters at the beginning.

Why the Other Three Extract Substring Activities Won’t Replicate Build Dynamic String

The other three Utility Actions are: Extract Substring from End of String, Extract Substring from Start of String, and Extract Substring of String from Index with Length. You can see these three activities and their parameters in the screenshot here (in the order I listed them):

You’ll notice that each one has a variable for the number of characters. If you leave this at 0, your variable will contain an empty string. Also, if you enter a number that is greater than the length of your string, it will contain an empty string! So, these activities are only useful if you know the exact length your output string needs to be; this is very unlikely if you are building a dynamic string (it is dynamic, remember). These actions will only return a value if the number of characters parameter is less than or equal to the length of the string parameter.

So, there you have it. I hope you find this useful.

I was recently working with a client who needed to show employees’ fax numbers in their People Search results in SharePoint 2010. Several months ago I got this working for them in SharePoint 2007, but it had to be configured all over again after the upgrade to SharePoint 2010. It was also more involved to set up in SharePoint 2010. I didn’t see where anyone else had documented how to show other fields in People Search results, so I thought I would share how I did this here.

Add Fax Numbers to User Profiles

Before you can expose any information, there must be information to expose. So edit your own profile (or a test user’s profile) to ensure it contains the information you want to see. In the screenshot here, you’ll notice that Connie Watson has edited her profile to contain her Fax number.

Configure the Fax Field to be Indexed

It’s possible that the field you want to add is already being indexed, but you’ll probably need to tell SharePoint to index the new field. Log into Central Administration. Click on Application Management > Manage Service Applications > User Profile Service Application. On the Manage Profile Service page, click on Manage User Properties.

In the list of Profile Properties, scroll down to find the field you want to add. You’ll find the Fax number field in the Contact Information section. Hover your mouse over it and select Edit from the drop-down menu.

On the Edit User Profile Property page, make a note of the actual name of the field and the data type in the Property Settings section. You’ll need to remember this for later steps. Here you’ll notice that the Fax number field has a Name of “Fax” and the Type is “string (Single Value)”.

Scroll down to the Search Settings section. Check the box beside Indexed. If this isn’t checked, the field won’t be crawled and it won’t be available to the People Search Results web part. When you are done, scroll to the bottom and click the OK button.

Add the Fax Fields to the Metadata Properties to be Searched

Although you’ve told the User Profile Service that this field is to be indexed, you still have to tell the Search Service to index it.

In Central Administration click on Application Management > Manage Service Applications > Search Service Application. On the Search Administration page, look in the left-hand navigation, scroll down to the Queries and Results section, and click on Metadata Properties.

Scroll through the pages of properties and look carefully to make sure that the field you want to add isn’t already listed. The Fax field isn’t listed by default. On the Metadata Property Mappings page, click on New Managed Property.

On the New Managed Property page, enter a name for the field. I named it “Fax” to be consistent. Enter a description if you want and select the appropriate data type. I selected Text since the Fax number data type I made note of on the Property Settings page “string (single value)”. Because the data type was a “single value” I left the box unchecked her for “Has Multiple Values.”

In the Mappings to Crawled Properties section, click on the Add Mapping button.

In the Crawled Property Selection dialog, type “Fax” for the Crawled property name and click the Find button. You’ll notice there are three properties that are shown. I added all three of them. You’ll have to add them one at a time though. So, select one of them, then click OK, then click the Add Mapping button again and repeat the process until you’ve added all three of them.

You should see all three Fax properties listed. Scroll to the bottom of the page and click the OK button.

Start a Full Search Crawl

Navigate back to the page where you manage the Search Service Application. In the left hand navigation, in the Crawling section, click Content Sources. Hover your mouse over the content source that contains the User Profile information (it’s probably named “Local SharePoint sites,” and select Start Full Crawl.

Add the Fax Column to the People Search Core Results Web Part

Navigate to the Search Center site and perform a People Search. When you see the people search results page (peopleresults.aspx), click on Site Actions –> Edit Page. Locate the People Search Core Results web part and click Edit Web Part.

In the Tool Pane, expand the Display Properties section and uncheck the box to Use Location Visualization. This will enable the Fetched Properties field, XSL Editor button, and Parameters Editor buttons (these options will be greyed-out and therefore unavailable until you uncheck this box).

Click in the Fetched Properties text box, press CTRL-A on your keyboard, then press CTRL-C to copy all the text to your clipboard. Open Notepad (or your favorite text editor) and paste in the text you copied. Locate the information for the WorkEmail, copy this section.

Paste this section back right after the place where you copied it. After you’ve pasted in that section, change WorkEmail in what you pasted to Fax (or the name of the field you want to add). It is very important that everything stays on one line so be careful not to insert any line breaks when you do this.

After you’ve changed the name of the field you want to add, copy all the text again and paste it back into the Fetched Properties field of the People Search Core Results web part. After you click in the Fetched Properties field, be sure to do a CTRL-A to select all the text that is there before you past in your text or else you will have invalid markup in the box. Scroll to the bottom of the Tool Pan and click the Apply button.

Caution: After editing the Fetched Properties field, you may see an error that says “Property doesn’t exist or is used in a manner inconsistent with schema settings.”

If you see this error, it’s probably because you skipped at least one of the previous steps to add the information you want to see to the profile of at least one user, set the column to be indexed, added the column as a searched Metadata Property, and performed a full search crawl after doing all three of those. It is also possible that the full search crawl has not yet finished. Go back and check that the work you did there was saved properly and that the full crawl has finished. If all these have been done the error should go away.

Edit the XSLT to display the Fax Number

Whew! You’re in the home stretch now! Just one more thing to do… actually show the fax number to the users. You should still have the Tool Pane open to edit the properties of the People Search Core Results web part. In the Display Properties section, click on the XSL Editor button.

You’ll need to make a few changes to the XSL code. I find it easier to copy all the XSL code from the browser dialog Text Editor and paste it into Notepad or some other editor. I also think it’s a good idea to copy the existing XSL code to another text file and save it so you’ll have a backup you can quickly paste back in here to restore it to the default in case you really mess something up—not that you would ever do that, of course.

Because I want the Fax number to appear below the user’s office number, I used the Find feature of Notepad to find the sections that referenced “workphone” and then duplicated those references and changed workphone to fax as needed.

About a third of the way down, you’ll see a section of variables that begin with “has”. This is used by the XSL to indicate if a property exists or not (a particular profile has a work phone or has a fax,.. get it?). Copy the line for the haswph variable and paste the copy on the next line. Edit the line in two places to change the workphone (wph) field to fax.

About half way down, you’ll see a section with several xsl:if statements that test for the $has variables. One of those is for the “workphone” field. Copy these four lines and paste a copy of them below where you copied them from. Change the references to “wph” and “workphone” to reference “fax” instead.

When you’re done, paste all the XSL code back into the browser dialog text editor and click the Save button. In the Tool Pane, click the OK button to save the changes to the web part and close the tool pane. On the Page ribbon, click the Save & Close button to save the changes to the page.

Perform a people search for a person who has a fax number and notice that the fax number now appears in the results. You did it!

Add Labels Beside the Properties

Although the Fax number is now showing up, it’s not very obvious that one number is a fax number and the other is a phone number. It would help if there were labels to indicate which number is which. This is easy to add though.

Once again, click on Site Actions –> Edit Page. Locate the People Search Core Results web part and click on Edit Web Part. In the Display Properties section of the Tool pane, click on the XSL Editor button.

Locate the xsl:if sections that display the Work Phone and Fax numbers. After the ‘li’ element and before the ‘xsl:apply-templates’ element, add the markup you want to use for a label. I used <strong>Fax: </strong> as shown in the screenshot here.

When you’re done, paste all the XSL code back into the browser dialog text editor and click the Save button. In the Tool Pane, click the OK button to save the changes to the web part and close the tool pane. On the Page ribbon, click the Save & Close button to save the changes to the page.

Perform a people search for a person who has a fax number and notice that labels now appear before both the Phone number and the Fax number.

Now go take a break and celebrate! You’ve earned it!

One of the most convenient ways to enter or edit the metadata for a Word document is to use the Document Information Panel (DIP). It’s also very easy for a user to accidentally (or sometimes on purpose) to hide the Document Information Panel. To close the Documents Information Panel, simply click on the “X” to close it as shown here.

Unfortunately, once the DIP has been closed, it’s not so obvious how to show the DIP again. However, it’s not difficult to open the DIP either. Simply click on File > Info > Properties > Show Document Panel.

There you have it, 4 simple clicks to show the Document Information Panel in Word 2010.

I recently needed to write a SharePoint Designer workflow to send a reminder email 12-hours before the end of a multiple-day event. It’s easy to use the Pause Until Date activity to pause until the necessary time, but it’s not so obvious how to calculate the date and time ithe workflow needs to pause until.

The easiest way to calculate the Pause Until date and time was to create it as a calculated column in SharePoint. Note: although the screenshots in this post are from SharePoint 2010, the principles are the same in SharePoint 2007.

In the Name and Type section, for the name of the column name I used DateTime for PauseUntil. I selected the Calculated (calculation based on other columns) data type.

In the Additional Column Settings section, I temporarily entered a formula of =[End Time]-1, selected Date and Time as the data type, and selected Date & Time as the format.

You’ll notice in the screenshot here that there are three different End Times and the calculated column subtracts exactly 24 hours for the =[End Time] –1 formula.

If we divide 1 day by 24 hours and divide 24 hours by 60 minutes, we get 1/24/60 = 0.00069444444. So, if you change the formula to =[End Time]-1/24/60…

you’ll notice that the dates and times in the Date for PauseUntil column are exactly one minute before the dates and times in the End Time column.

What this means, is that to add or subtract a certain number of minutes from a date and time field, we just need to multiply 1/24/60 by the number of minutes we want to add or subtract. In my problem, I needed to subtract 12 hours which is 12*60 or 720 minutes. Let’s see if that works…

Yep, all the calculated times are exactly twelve hours before the end time; and if you’re wondering, crossing midnight times doesn’t cause any problems for these calculations.

So, if you need to add or subtract a certain number of hours or minutes in a calculated column in SharePoint, you can simply multiply 1/24/60 by the number of minutes you need to add or subtract to achieve any value you need.

Special thanks goes to Mike Smith for his response in this discussion board thread for originally sharing this tip. I wrote this post in an attempt to break it down a little bit more and add screenshots in hopes that some others might find the information a little more understandable.