Showing posts with label SharePoint 2010. Show all posts
Showing posts with label SharePoint 2010. Show all posts


SharePoint Calculated Columns – Convert a Month Name to a Number

Here are three solutions for when you have a column with a three letter abbreviation of a month name, and you want the month number,

Each of the following assume that you have a "Single Line of Text" or "Choice" column named "Month".

A long IF statement:


Using the FIND() Function:

       ) -1 
 ) / 4

FIND returns the position of the matching text. "JAN" returns five. If we subtract one and then divide the FIND number by four, we get the month number.

Finding the Month Number Using Three or More Month Letters

       )  -1
 ) / 10

This uses the same idea, but the month names are spelled out and are padded with spaces to make each one nine characters long. (Ten with the commas.)

Here’s the result:


Now available on Amazon!



SharePoint Calculated Columns and Validation Formulas – The Book!

Available on Amazon!

It only took me a year… and it grew from a little booklet to over 200 pages… but its finally done!

Bought the book? Post any questions, bugs, typos and suggestions to this blog post!

Everything you need to know about SharePoint formulas for SharePoint 2010 to 2019 and SharePoint Online / Office 365!


This book is for the SharePoint “power user” who needs a better understanding of how SharePoint formulas work, where they don’t work, and how they are created. While at first glance SharePoint formulas appear to be simple calculations, they are both quite powerful and have weird limitations. In this book we will explore the basics of creating Calculated Columns and Validation formulas with some boring details, and over one hundred examples. We also explore workarounds for many of the limitations by using SharePoint Designer workflows and a few tricks!


Over 100 Examples!

A how-to book of formulas would not be too useful without a few examples. I've been collecting these for years. They've come from classroom questions, forum questions, and my own SharePoint sites. Now they are all in one place…

  • · Over 60 Calculated Columns examples
  • · Over 30 Column Validation examples
  • · 11 List/Library Item Validation examples
    (and every one of the Column Validation examples can be used here.)
  • · 7 Calculated Column Default Values examples
  • · 15 Workflow “workarounds” for things SharePoint formulas can’t do

Scroll on down for a complete list of the examples.

Who is this book for?

Anyone who creates and customizes lists and libraries. These include: SharePoint on premise Farm Administrators, Office 365 SharePoint administrators, Site Collection Administrators, Site Owners, Power users, and Developers.


Where are formulas used in SharePoint?

  • Calculated column formulas derive data from other columns in the same list item. These can be simple calculations and text manipulations, or complex Excel-style formulas.
  • Column validation formulas are used to create custom validations for user entered data. These can be used to make sure that "quantities" are not negative and vacation requests are for dates in the future.
  • Column default formulas, while very limited, can auto-fill columns with a date 30 days in the future or a message based on the month or day of the week the item was added.
  • List / Library validation formulas are used to check the values from multiple columns to verify the relationship between two or more columns. Examples include making sure a task start date is before the task end date, or to make sure an entry has a "price per pound" or a "price each", but not both.

Workflow Workarounds?

SharePoint formulas only work with a few column types while SharePoint workflows can access just about any column type. So, with a little workflow work your formulas can make these columns available to SharePoint formulas. There's over fifty pages of workflow workaround tips:

  • Workaround for People and Groups
  • Getting Data from a Person or Group Column
  • Getting a User’s Manager
  • Workaround for the SUBSTITUTE Function
  • Workaround for Getting View Totals from Calculated Columns.
  • Workaround for Adding Images to a Column
  • Workaround for Multiple Lines of Text
  • Workaround for Lookup Columns
  • Lookup a Single Item (No checkboxes)
  • Extract Several Lookup Columns into a Single Line of Text
  • Workaround for Multivalued Choice Columns
  • Counting Items Selected in a Multivalued Column
  • Workaround for Managed Metadata columns
  • Workaround for Single Valued Managed Metadata Columns
  • Workaround for Multi Valued Managed Metadata Columns
  • Workarounds for Attachments


Table of Contents

  • Read Me First
  • Tips for Formulas
  • Calculated Columns
  • Calculated Column Examples
  • Calculated Default Columns
  • Column Validation Formulas
  • Column Validation Examples
  • List/Library Item Validation
  • List/Library Item Validation Examples
  • Other Forms of Validation
  • Workflow Workarounds
  • Error Messages


Calculated Column Examples:·

·        Fun stuff that may not work for you:

o   Add HTML to a Calculated Column

o   Add icons or pictures to a Calculated Column

o   Building an Address (String Concatenation)

o   Create a Bar Chart Column

·        Columns for Views

o   View Filtering on a Calculated Column

o   Group by Year

o   Group by Month and Year

o   Group by Year Plus Month

o   Grouping on an Algorithm

·        Numbers

o   Adding Leading Zeros to a Number

o   Scientific Notation

o   Roman Numerals

·        The IF Function and Boolean Logic

o   Calculating a Discount using Nested IFs

o   Working Around Nested IF Limits

o   Convert from State Codes to State Names

o   ANDs and ORs - Approve if all approved, or reject if any one rejects

·        Test for values

o   Testing for a Range of Dates

o   Testing for Empty Columns

o   Testing for Errors

·        Summing and Counting Columns

o   Counting Yes/No Columns

o   Average

o   MIN and MAX

·        More on Numbers

o   Raise a Number to a Power

o   Rounding Numbers

·        Working with Text

o   Combining Text Columns

o   Display First Name and Last Name in a Single Column

o   Creating Title Capitalization

·        Return the Right Data Type!

·        Dates

o   Subtracting Dates

o   Finding a Date One Year in the Future

o   Change Date Formatting

o   Fiscal Year

o   Week Number

o   Day of the Year

o   First Day of the Current Month

o   First Day of the Next Month

o   Last Day of the Current Month

o   Last Day of the Previous Month

o   Last Day of the Next Month

o   Third Tuesday of a Month

o   Skipping Weekend Days

o   Next Workday “X” Days in the Future

o   Simple Solutions for 5, 10, 15 Working Days in the Future

o   Solution for Any Number of Working Days in the Future

o   Working Days

o   Number of Working Hours Between Two Dates

·        Formatting and Conversions

o   Formatting Numbers

o   Adding Text to Numbers

o   Adding Special Symbols (¥, £, etc.) to TEXT() Number Formats

o   Converting Numbers to Fractions

o   Financial Calculations

·        Random Numbers and Messages

o   Creating Random Numbers (Using NOW)

o   Creating Random Messages (using CHOOSE)

·        A Calculated Task Status

·        Great Circle Distance from Longitude and Latitude

·        Simplify a Workflow by Using a Calculated Column

Column Validation Examples

·        Limit time for user updates.

·        Boolean operations

o   Examples Using “OR”

o   Examples Using “AND”

o   Examples Using “AND” and “OR”

o   Examples Using “NOT”

·        Testing for Empty Columns

o   Yes/No

o   Dates


·        Working with Dates

o   Date Must be in the Future

o   Date Must be in the Future “x” Days

o   Date Must be in the Future “x” Days and not on a Weekend

o   Test Against a Specified Date

o   Date Must be Between Now and the End of the Current Year

o   Date Must be Within the Next 30 days

o   Date Must be the Last Day of the Month

o   Date Must be the First Day of the Month

o   Date Must be the Third Tuesday of the Month

o   Date Can Only be a Weekday

o   Date Can Only be Monday or Wednesday

o   Entered Date Must be for this Year

o   Date Must be Current Or Next Month Only

·        Working with Numbers

o   Testing for Numbers in Custom Increments

o   Limit the Number of Decimal Places Entered

·        Working with Text

o   Testing for Text Length

o   Testing for a Valid State/Province (or other code)

o   Test to Require an Uppercase or Lowercase Letter 

·        Examples Using Pattern Matching

o   Must start with an "a" or "A" and the third character must be a "c" or "C"

o   Match a Phone Number Pattern of xxx-xxx-xxxx

o   Match a Phone Number Pattern of xxx-xxx-xxxx and Limit the Length

o   Match a Phone Number and Make Sure Only Digits Have Been Used

And many more!


SharePoint Dates are Always Date AND Time

A Quick SharePoint Date Tip!

When you create a Date column you have the choice of Date and Date & Time.


Note the keyword “Format” in that option. Even if you select “Date Only”, your users can still type, or copy and paste, a date and a time and it will be stored as a date and time. But… only the date will be displayed.

Times are a Fraction of a Date

Times are represented as parts of a day.


Converting a Date and Time to a Date

As you saw above, SharePoint dates are always dates and times. Even if you set a column to be “Date”, users can still type, or copy and paste, Date and Time values. Columns formatted as “Date” may display as just a date, but they will still be filtered and calculated as a Date and Time.

If you are calculating the number of days between two dates you might write “[Date2]-[Date1]”, which will produce the expected result if both dates are true Date values. If Date2 is “1/16/2018 6:00 AM” and formatted as “1/16/2018”, and Date1 is “1/14/2018” subtracting the two will return 2.25 days, not the hoped for 2 days.

There are several ways of converting a Date and Time to a Date:

  • Use the DATE function:
        DATE( YEAR( [SomeDate] ), MONTH(( [SomeDate] ), DAY( [SomeDate] ) )
  • Use the ROUND function:
        ROUND( [SomeDate], 0) (This will round up or down depending on the value)
  • Use the FLOOR (round down) function:
        FLOOR( [SomeDate], 1)
  • Use the CEILING (round up) function:
         CEILING( [SomeDate], 1)
  • Use the INT function (which also rounds down):
        INT( [SomeDate])

I prefer the INT as it is the least typing and I most often need to round down to remove the fractional part of a Date and Tim   


SharePoint TEXT() Function Bug for Scientific Notation

In Excel you can use the TEXT function to display very large and very small numbers in scientific notation.



Due to a bug in SharePoint Calculated Columns, the TEXT function adds some extra zeros to the end of the formatted number.


Here’s a workaround…

Assuming you want the equivalent of TEXT([Number],"0.00E+00")

=LEFT(TEXT(Number,"0.00E+00"),6) &

This part:


Pulls off the "3.70E+".

This part:


Pulls off the "9" and formats it as "09”.

To change the format, adjust the number of zeros in the obvious places, and adjust the "6", "7" and "8" as needed. Here two examples to help you figure out which numbers to change.

For the equivalent of TEXT([Number],"0.00E+000")

=LEFT(TEXT(Number,"0.00E+000"),6) &

For the equivalent of TEXT([Number],"0.000E+00")

=LEFT(TEXT(Number,"0.000E+00"),7) &

Here the "0.00E+00", "0.00E+000" and "0.000E+00" results.


Added to

I just listed this as a bug. If you would like to vote for a fix, go here:

As the feature has minimal usage, and has been there forever, I don't expect too much... ;-)



Adding Fractions (1/4) to SharePoint Calculated Columns

(The following works SharePoint 2010, 2013, 2016, SharePoint Online, and most likely, 2019!)

0, 1/8, 1/4, 3/8, 1/2, 5/8, 3/4, and 7/8

This is a sample from my soon to be published book “SharePoint Calculated Columns and Validation Formulas”. If you would like to see a lot more about what you can do with Calculated Columns, and maybe win a free book, attend my session at SharePoint Cincy 2018 on April 10th!

In Excel you can get fractions using =TEXT(yourNumberColumn,"# ?/?"):   


The same function in SharePoint will round off the number, and for each of the above values, return 5. If you need fractions then you will need to write your own function to create them. Below we have several versions of this function depending on the fractions and formatting needed.


Function to return the fractions 0, 1/4, 1/2, and 3/4

Note: Read to the end to see a shorter version that uses CHOOSE instead of nested IFs.

Just a couple of nested IFs…

=IF( yourNumberColumn - INT(yourNumberColumn) = 0.25,
          INT( yourNumberColumn) & " 1/4",
   IF( yourNumberColumn - INT(yourNumberColumn) = 0.5,
           INT( yourNumberColumn) & " 1/2",
     IF( yourNumberColumn - INT(yourNumberColumn) = 0.75,
              INT( yourNumberColumn) & " 3/4",
         INT(yourNumberColumn) ) ) )

The above formula only works if the entered values end in exactly 0, .25, .5 and .75. If you need your function to round off to the nearest 1/4th, replace the first value in each IF with:

    ROUND( yourNumberColumn * 4, 0 ) / 4

=IF( ROUND( yourNumberColumn*4, 0 ) / 4 - INT(yourNumberColumn) = 0.25,
     INT(yourNumberColumn) & " 1/4",
   IF( ROUND(yourNumberColumn * 4, 0 ) / 4 - INT(yourNumberColumn) = 0.5,
       INT(yourNumberColumn) & " 1/2",
     IF( ROUND(yourNumberColumn * 4 , 0 ) / 4 - INT(yourNumberColumn) = 0.75,
         INT( yourNumberColumn) & " 3/4",
         ROUND( yourNumberColumn,0) ) ) )

If you would like to see ¼ instead of 1/4 then use the CHAR function to get those symbols. While the ¼, ½, and ¾ characters are available, 1/3 and 1/8 are not.

  • Replace " 1/4" with " " & CHAR(188)
  • Replace " 1/2" with " " & CHAR(189)
  • Replace " 3/4" with " " & CHAR(190)
=IF( ROUND( yourNumberColumn * 4, 0 ) / 4 - INT(yourNumberColumn) = 0.25,
     INT(yourNumberColumn) & " " & CHAR(188),
   IF( ROUND(yourNumberColumn * 4, 0 ) / 4 - INT(yourNumberColumn) = 0.5,
       INT(yourNumberColumn) & " " & CHAR(189),
     IF( ROUND(yourNumberColumn * 4, 0 ) / 4  -INT(yourNumberColumn)=0.75,
         INT( yourNumberColumn) & " " & CHAR(190),
         ROUND( yourNumberColumn, 0 ) ) ) )

Function to return the fractions 0, 1/8, 1/4, 3/8, 1/2, 5/8, 3/4, and 7/8

To add support for eighths we need to add four more IFs. If we change the number used in our rounding code from 4 to 8, and add a few lines, then web can display values to the nearest eighth.

=IF( ROUND(yourNumberColumn * 8, 0 ) / 8 - INT(yourNumberColumn) = 0.125,
       INT(yourNumberColumn) & " 1/8",
   IF( ROUND(yourNumberColumn * 8, 0 ) / 8 - INT(yourNumberColumn) = 0.25,
         INT(yourNumberColumn) & " 1/4",
     IF( ROUND(yourNumberColumn * 8, 0 ) / 8 - INT(yourNumberColumn) = 0.375,
            INT(yourNumberColumn) & " 3/8",
        IF( ROUND(yourNumberColumn * 8, 0 ) / 8 - INT(yourNumberColumn) = 0.5,
              INT(yourNumberColumn) & " 1/2",
           IF( ROUND(yourNumberColumn * 8, 0 ) / 8 - INT(yourNumberColumn) = 0.625,
                 INT(yourNumberColumn) & " 5/8",
              IF( ROUND(yourNumberColumn * 8, 0 ) / 8 - INT(yourNumberColumn) = 0.75,
                    INT(yourNumberColumn) & " 3/4",
                 IF( ROUND(yourNumberColumn * 8, 0 ) / 8 - INT(yourNumberColumn) = 0.875,
                      INT(yourNumberColumn) & " 7/8",
    ROUND(yourNumberColumn, 0 ) ) ) ) ) ) ) )

Now we have fractions to the nearest 1/8th.


A Much Shorter Version that uses CHOOSE

Here is a much shorter version of a formula to display a number as a fraction:

=ROUNDDOWN( yourNumberColumn + 0.062499, 0 ) &
  CHOOSE( ( ROUND( yourNumberColumn * 8, 0 ) / 8 
            - INT(yourNumberColumn) ) * 8 + 1,
          ""," 1/8"," 1/4"," 3/8"," 1/2", " 5/8", " 3/4", " 7/8",""

This uses CHOOSE to pick the text to display for “less than 1/8th”, “1/8th” … “7/8th” and “more than 7/8th”. (Nine possible values.)

To get the fraction:

  1. Round the number to the nearest 1/8th:
        ( ROUND( yourNumberColumn * 8, 0 ) / 8 - INT(yourNumberColumn) ) * 8
  2. And as CHOOSE starts with 1 and not 0, add one to the result:
        ( ROUND( yourNumberColumn * 8, 0 ) / 8 - INT(yourNumberColumn) ) * 8 + 1
  3. Use CHOOSE to pick the text:
        CHOOSE( … , ""," 1/8"," 1/4"," 3/8"," 1/2", " 5/8", " 3/4", " 7/8","")
  4. Concatenate that to the rounded down number:
        ROUNDDOWN( yourNumberColumn + 0.062499, 0 ) & …
    The “+ 0.062499” is added to deal with the last 1/16th after “7/8th” so we round up to the next higher number.




Adding Roman Numerals to SharePoint


In a Calculated Column

Assuming an integer in a numeric column named the "TheNumber", just add this equation to a Calculated Column:



You can also select alternate formats:



In a Page, Content Editor Web Part or a Rich Text Multiple Lines of Text column.

The Rich Text ribbon offers buttons to create bullets and numbers, but does not have one to select the other list formatting options. With a little HTML edit you can get any of the HTML list styles.

  1. Create the numbered list as usual. (1., 2., 3....)
  2. Click the "Edit Source" button in the ribbon.
  3. Find the <ol> tag and change it to <ol type="I">. (Use a lower case "i" for lower case Roman numerals.)
  4. Click OK.


Additional List Styles

When starting with a numbered list you can choose any of the HTML ordered lists (<OL>) types:

  • type = "1" – numbers
  • type = "A" – Uppercase letters: A, B, C
  • type ="a" – Lowercase letters:  a, b, c
  • type – "I" – Uppercase Roman numerals: I, II, III
  • type = "I" – Lowercase Roman numerals:  i, ii, iii

When starting with a bulleted list you can choose any of the HTML unordered lists (<UL>) types:

  • disc
  • circle
  • square
  • none

You can also use your own custom images. See:



A SharePoint Calculated Column for all 50 States (workarounds for nested IF limits!)

Just in case you might ever need a formula to convert state abbreviations into state names…

  • You will need 50 nested IFs,
    • but SharePoint 2007 and 2010 only allows 7, and 2013 and later only allows 19.
  • You will need a little more than 1300 characters in the formula,
    • but SharePoint 2007 and 2010 only allow 1024. (2013 and later are around one billion!)

The trick for the IFs is to only nest 19 at a time and return a state name, or an empty string (""), and then concatenate another 19 nested IFs that return a state name, or an empty string… repeat until done! If you are using 2007 or 2010, then nest 7 at a time, and then concatenate another 7.

But what about the 2007 and 2010 1024 character limit? Renaming the "state" column to just one letter brings the formula down to 1111 characters, but that's still more than the 1024 allowed. Solution? Three Calculated columns. The first has the formulas for the first 25 states (in multiple IF nestings of 7 or less) that returns a state name or an empty string, The second has the next 25 states and returns a state name or an empty string. The third just concatenates the first two columns.

Here's the formula for SharePoint 2013 and later for a column named "State":

if(State="ME","Maine",""))))))))))))))))))) &
if(State="NH","New Hampshire",
if(State="NJ","New Jersey",
if(State="NM","New Mexico",
if(State="NY","New York",
if(State="NC","North Carolina",
if(State="ND","North Dakota",
if(State="PA","Pennsylvania",""))))))))))))))))))) &
if(State="RI","Rhode Island",
if(State="SC","South Carolina",
if(State="SD","South Dakota",
if(State="WV","West Virginia",


SharePoint Audiences are not Security!

In a nutshell… The SharePoint Audience feature is not security… ever. Audiences are used to filter (hide), not secure.

Some may consider the use of Audiences as “security by obscurity”, but it is not security.

From my SharePoint Security book…  (on Amazon – 2013/2016 version coming soon.)


SharePoint audiences are used to target content to specific groups of people by hiding it from those who don’t need to see it. These groups can be SharePoint groups, Active Directory security groups, Active Directory distribution lists and SharePoint global audiences that are based on user profile data.

Audience targeting can be used with:

  • List and library items, but only when displayed using a Content Query Web Part (part of the Publishing feature).
  • Entire web parts.
  • Top Link and Quick Launch navigation links. (when Publishing features are enabled)

Note: The Audiences feature is only available with SharePoint Server Standard and Enterprise editions. SharePoint Foundation has no support for Audiences. (Audiences is part of User Profile Services.)

Audiences are not security!

Audiences are usually described as being used to "target" content to a selected group. Audiences could also be described as being used to hide content from all users except for the target audiences. This second form sounds like security, but absolutely is not. While a list item or document web part might have a target audience, and non-audience members won't see the web part, if it is not otherwise secured they can still get to the item by using a direct URL or find it from search.

The Audience feature should be thought of as a filtering option, not security.

To filter list items using an Audience

List and library items can be filtered using the Audience feature using the Publishing feature’s Content Query Web Part. While the regular list web parts have an Audience feature, that feature hides the entire web part, not selected items. The Content Query Web Part is added to a site collection when you enable the Publishing features.

Four steps are required to filter list content using an Audience:

· Create a publishing site or enable the Publishing Infrastructure feature on a site collection.

· Enable the Audience feature on the list.

· “Tag” list items by Audience.

· Display the list using the Content Query Web Part.

Step 1: Enable the SharePoint Server Publishing Infrastructure Site Collection feature

First make sure there are no other reasons to not enable the Publishing features. (Policy, support, governance, etc.)

1. Go Settings (gear), Site Settings of the top level site of the site collection.

2. In the Site Collection Administration section click Site collection features.

3. If not already activated, activate the SharePoint Server Publishing Infrastructure feature.

Step 2: Enable the Audience feature on the list or library

1. Go the list or library, click the LIST or LIBRARY tab in the ribbon.

2. Click List Settings or Library Settings.

3. Click Audience targeting settings.

4. Checkmark Enable audience targeting.
A new field named Target Audiences will now be displayed in the New and Edit pages for the list.

5. Click OK.

Step 3: “Tag” list items by Audience

1. Edit the properties of a list or library item.

2. In the Audience area click the Browse button ( ) and select an Audience.

3. Save the changes to the item.

Step 4: Display the list using the Content Query Web Part

1. Move to your page where you want to display the web part.

2. Edit the page.

3. Click the Insert ribbon tab and the Web Part button.

4. Click on the Content Rollup category and then click the Content Query web part. (If the web part is not listed then you do not have the SharePoint Server Publishing Infrastructure site collection Feature enabled.)

5. Click Add to add the web part to the page.

6. Click the web part’s dropdown menu and click Edit Web Part.

7. Expand the Query section.

8. Select the Source (the scope) for the rollup from one of the following: Show items from all sites in this site collection, Show items from the following site and all subsites, or Show items from the following list.

9. Select the List Type and the Content Type to select the content to display from the Source selected above.

10. In the Audience Targeting section checkmark Apply audience filtering.

11. Optionally add filters or Presentation options.

12. Click OK to save your web part changes.

13. Save the page and test the results.

Note: the Target Audience option in the Advanced section of the web part’s property panel is used to control if the entire web part will be displayed for an audience.

Search Web Parts vs the CQWP

Microsoft currently recommends using the Search Web Parts in many of the places that we might have used the CQWP. While search uses cached content, and can be quite a bit faster, the data is only as current as the last search crawl. (I.e. Completed tasks may still display as incomplete.) The CQWP is always using live data and can be Audience filtered.

Resources for the CQWP:

Display data from multiple lists with the Content Query Web Part: (or just do a web search for “Display a dynamic view of content on a page by adding the Content Query Web Part”)

To show web parts for an Audience

You can use Audiences to hide an entire web part from all users except for the selected audiences. Simply edit the web part, expand the Advanced section and select an audience.

Note: The SharePoint Server Publishing Infrastructure Site Collection feature is not needed for web part Audience filtering.

To display a Quick Launch or Top Link Bar link for an Audience

Links in the Quick Launch and the Top Link Bar can be filtered by Audience when the Publishing Infrastructure feature has been activated. Once this feature has been activated the Quick Launch and Top Link Bar options are replaced with a single Site Settings option named Navigation. In the Navigation page Quick Launch is called Current Navigation and the Top Link Bar is called Global Navigation.

Enable the SharePoint Server Publishing Infrastructure Site Collection feature:

1. Go Settings (gear), Site Settings of the top level site of the site collection.

2. In the Site Collection Administration section click Site collection features.

3. If not already activated, activate the SharePoint Server Publishing Infrastructure feature.

To filter navigation links:

1. Go Settings (gear), Site Settings.

2. In the Look and Feel section click Navigation.

3. Scroll down to the Navigation Editing and Sorting section of the page.

4. Add or edit a Heading or a Link.

5. In the Audience area click the Browse button ( ) and select an Audience.

6. Set the Title, URL and other options as desired and click OK.

7. Test! The new navigation item should only be displayed for the selected Audiences.


Creating Random Numbers in SharePoint Calculated Columns


One of my examples for tonight’s Cincinnati SharePoint User Group meeting! See you there!


I wanted to add a "motivational" message to a list of new sales. To be "fair" (i.e. I did not want to think and create a good algorithm!) I wanted the messages to be random. Something like this:


But… Calculated Columns do not support the Excel RAND() or RANDBETWEEN() functions.


So, how to get a random number???

Calculated columns do support the =Now() function. This returns a numeric value that represents the current date and time. If formatted as a Date, or Date and Time, then you will see the current date. But, if you format it as Single Line of Text you will see something like: 42,691.3977137731, or a few seconds later: 42,691.3983521875. The last number starts to look like a random number! And if accurate, it changes every .0000000001 of a day, or about every 0.00000864 seconds. Close enough for me.


Get a random number between 0 and 9.

This one looks easy, just pull off the last digit from NOW()!

    =RIGHT( NOW() ,1)

But.. there’s one flaw with this… The last digit of a fractional value is never zero!  (I.e. you will never see .111111110 unless custom formatted.)

So we need to pull off the next to last digit!

  =LEFT( RIGHT( NOW() ,2) ,1 )




Get a random number between 1 and 5

With just a little math we can limit the range a bit. As we don’t want the zero value we can skip the LEFT function for this one.

   =ROUND( RIGHT( NOW()) / 2+0.5 ,0)


Here’s a sample:



Get a random number between 0 and 999.

If you need bigger numbers, just return more digits:


As RIGHT creates a string (text), you will get leading zeros (“012”). To remove the leading zeros just do some math!

    = 0 + RIGHT(NOW(),3)


But… (there’s always a “but”), this will never return a value that ends with a zero. So… back to the LEFT function:

    =LEFT( RIGHT(NOW(),4), 3)

I.e. get the left three of the right four digits…



Random Messages?

This little exercise started out to create random messages. All we need to do is combine a random number with the CHOOSE function. As CHOOSE starts with item 1 and not item 0, we will need to add one to the random number.

   =CHOOSE( LEFT( RIGHT( NOW() ,2), 1) + 1, "Good Job", "Wow!", "Good Work", "Thanks!", "Could be better",
                      "Gold star for you!", "a free coffee for you!",":-)", "You are the MAX!","Do it again!" )




  • These are not guaranteed to be mathematically pure random numbers!
  • The values depend on the exact instant that an item is added to a list and will change with each edit. (But will not change with each view.)


Note to spammers!

Spammers, don't waste your time... all posts are moderated. If your comment includes unrelated links, is advertising, or just pure spam, it will never be seen.