SharePoint: Int Function

SharePoint int function in most programming languages is used to convert an “object” from one type to an integer. However, in the case of the SharePoint int function, it does a bit more than that. We’ll check below all these cases but, for now, let’s think of the int function as:

1. Converts, if possible, a datatype to int.
2. Round down the number

Let’s explore it a bit more and understand how it works

Usage

It follows a simple pattern.

1. Type to parse

First, we’ll look at the convert and then the round.

Convert

Let’s start with the simplest example. But, first, we provide a number and see what happens:

=INT([numberColumn])

15

will return 

15

So far, so good. The number is displayed correctly. There’s no conversion needed since the number is the common type, and it’s not a decimal value. Now let’s look at an example where we have a text column:

=INT([textColumn])

"15"

will return 

15

The number returned matches the one in the string, but what about if there are spaces before and after the numeric characters?

=INT([textColumn])

" 15 "

will return 

15

SharePoint does a great job at ignoring unnecessary characters.

Now the more “edge” cases. What if we have a time defined in the string?

=INT([textColumn])

"15:30"

will return 

0

"2:00pm"

will return 

0

It’s an unexpected result. We would think one of two things would happen. Either we get an error or a number that would somehow represent the time. But SharePoint returns zero. Be careful with these cases, since it’s a behavior that is strange, in my opinion. I’m suspicious that it tries to convert the date (see below), but since it rounding the number and the hours and minute are the decimal part, it returns zero. If you know the official reason, please let me know by email or Twitter.

How about dates?

=INT([textColumn])

"2021-10-01"

will return 

44470

The number may look strange, but it’s an “Excel” date number. SharePoint, as with Excel, stores internally the dates as numbers. I go into much more detail on “Convert Excel Number to Date” and “Convert Date to Excel Number”, but the short version is that Excel stores the number of days elapsed since 1st January 1900. So that’s where the number comes from.

Now let’s look at boolean columns:

=INT([booleanColumn])

false

will return 

0

true

will return

1

The results are consistent with what we would expect since “true/false” values are commonly known as “1/0” respectively.

Finally, how about choice columns? The answer depends. If the solution only accepts on option:

=INT([Choice])

2

will return

2

But if you enable “Allow multiple selections,” you can’t even select it in the “Edit Column” formula. It’s a good choice by Microsoft since the result won’t make sense anyway.

Round

As mentioned before, SharePoint returns the round version of the number, working the same way as the “RoundDown” Function.

Let’s start with the simplest example. We provide a number and see what happens:

=INT([textColumn])

15.4

will return 

15

It works fine, converting the string into a number. Now let’s provide a string with a decimal value:

=INT([textColumn])

"15.6"

will return 

15

We’re doing two things at the same time. First, the string will be converted to an int, and then it’s rounded down as well.

One could look at this and think that everything after the comma is being truncated, but that’s not quite the case. Let’s try the following case:

=INT([textColumn])

"15.6,3"

will return 

#Name?

If it were a truncation, the “Int” function above would return “15” as before and not an error. To prove this point further, let’s look at a negative number.

=INT([textColumn])

"-15.3"

will return 

-16

The number was indeed rounded down, so please take this calculation also into consideration.

Limitations

I like it a lot that it converts dates, but it’s a pity that it’s not converting the time correctly as well. It makes sense to work this way, but it may be super confusing for people to find a zero in the Function’s return.

Recommendations:

“Hidden” Validation Columns

Since SharePoint works with columns, you can create hidden “validation” columns with the functions validating the data. After that, you can expose the “message” columns that have helpful error messages or actions that they can take to solve the problem.

Always deal with errors.

Leaving them for the user to see is horrible, but also you can bring trouble to your code. If you don’t validate a column, it can be used in other calculations, and the error will cascade. You can have a mistake in one column, and the issue could be in whole different columns, so deal with issues where they happen.

Don’t nest it.

There’s no real reason to do it, but if you find yourself in a situation where you have nested “int” functions in a formula, you should review it and make everything more straightforward.

Sources:

Microsoft’s int Function Reference

Introduction to SharePoint formulas and functions

Back to the SharePoint’s list formula reference.

Photo by Scott Rodgerson on Unsplash