by The “hour function” is part of a suite of functions that deal with dates. It returns the part of the time; for example, 12:30 will return 12. You can provide a serial number or a string, but let’s check each case individually.
Usage
Let’s break it down into the types that you can use.
Date
The most common usage for this function is to provide a date. For example:
Date
3rd of June 2021 17:45
=HOUR([Date])
will return
17
It’s quite straightforward when it comes to dates. If the column is date-only, the function will return 0.
Number
The number is a bit more tricky. We need to provide a decimal number from 0 to 1.
For example:
Number
0.5
=HOUR(numberColumn)
will return
12
Why 12? Because it’s “half-day.” It’s strange but let’s look at another example. For example, if we want to get 6 pm. It’s 3 quarters of the day so:
Number
0.75
=HOUR(numberColumn)
will return
18
If the number is above 1, the function will return 0 if you don’t provide the decimal part.
Number
44176 (11th of December 2020)
=HOUR(numberColumn)
will return
0
But it will work properly if you provide the decimal part.
Number
44176,75 (11th of December 2020 6pm)
=HOUR(numberColumn)
will return
18
Finally, if the number is below zero, it will return an error:
Number
-12
=HOUR(numberColumn)
will return
#Name?
Pay special attention if you’re calculating the time somehow and deal with the error if it occurs.
Strings
You can also use string to parse your date. It’s quite error-prone, so use it sparely and always manage errors since the probability of not containing a valid time is high. Let’s look at some examples:
String
"2:00pm"
=HOUR(text)
will return
14
But we usually define dates as “2 pm,” for example. Let’s see what happens:
String
"2pm"
=HOUR(text)
will return
#Name?
Be especially careful with these cases. The string “2pm” is not a valid time. The string needs to contain the minutes component; that’s why it returns the valid value in the first string and not the second.
String
"15"
=HOUR(text)
will return
0
This is counterintuitive, but in the above case, we won’t get an error. If you provide the minute part of the time:
String
"15:30"
=HOUR(text)
will return
15
You’ll get the correct result. The seconds are optional, and the “hour” function will work if you provide them:
String
"14:40:32"
=HOUR(text)
will return
14
Limitations
You need to provide the minutes part, but you don’t need to provide the seconds. It’s also optional the “am/pm” part since you can provide the time in 0-24 format. In some cases, the function will return errors, and in others will return 0, so consider this when you’re building your formula.
Recommendations:
0 is not an error
Please note that if the function returns zero doesn’t mean that there’s an error. Since the default time is 00:00, it’s providing the calculation on the default value and returning the correct value of 0.
Above 0 is a date
If you provide a number above zero, without a decimal part, it will return 0 for the same reason as above. Any number above zero will be considered a date since SharePoint (like Excel) stores dates using a serial number that is the number of days elapsed from a date. I have an article that goes into detail and explains how to parse it in Power Automate. Check also the “day function” reference for more details on how SharePoint deals with dates.
Try to use dates if you can
Using dates as the parameter is the best way always to get the results that you want. Numbers, as you saw above, are messy and not very intuitive. If you represent the dates from 0-12, the result may not be super intuitive for you since it will return in a 0-24 format.
Strings are super messy.
Strings are the worse format that you can use. As you can see above, having strings in similar formats like “2pm” or “14” will return an error and 0, respectively.
I don’t think the “hour” function is a potential “natural language parser” where you insert a time in the format you want, and it will convert it. If you need to use them, deal with errors because you’ll have a lot of them. For example, if you try:
String
"noon"
=HOUR(text)
will return
#Name?
Avoid at all costs strings to get this type of information.
“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 to the user the “message” columns that have useful 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 own code. If you don’t validate a column, it can be used after other calculations, and the error will cascade. You can have an error 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 the “hour” function in a formula, you should review it and make everything more straightforward.
Sources:
Microsoft’s HOUR Function Reference
Introduction to SharePoint formulas and functions
Back to SharePoint’s list formula reference.
Photo by Agê Barros on Unsplash
