Dealing with dates and times is hard, especially considering time zones, formats, and regional settings. So having functions that do this for us is a godsend, so today, we’ll look at the “startOfHour” function. This function will get any date and return a new one at the beginning of the hour.
Let’s see how to use it.
Where to find it?
You can find the function in every action where a formula is supported. For example, let’s look at a “Compose” action:
As you can see, we can auto-complete by using the “tab” key.
Now that we know where to find it let’s look at how to use it.
It follows a simple pattern.
- Format (optional)
Let’s start with a simple example, using the utcNow function.
startOfHour(utcNow()) will return 2022-09-07T09:00:00.0000000Z
You can also provide your timestamp as follows:
startOfHour('2022-09-07T01:32:02.0068700Z') will return 2022-09-07T01:00:00.0000000Z
The date will always be converted to the reference start of the hour.
The format parameter
The format only provides you with the result that means something to you. I go into a lot of detail here on converting a string into your language. If you’re using the startOfHour function to display data to your users, I strongly advise you to show it in a way they understand. Again, I go over a lot of detail here , but you can use the format as follows:
startOfHour('2022-08-31T01:32:02.0068700Z','MMMM dd, yyyy HH:mm') will return August 31, 2022 01:00
It looks much better to a user than the previous date, right?
Notice that we cannot provide the locale meaning that the results will always default to English. If you want to format the date, I recommend not doing it here but using the formatDateTime function.
So why use it?
The answer is simple. To avoid multiple steps when you can have only one. Think, for example, if you have a Flow that triggers every hour. The triggers are usually precise, but Microsoft states that the schedules can get late up until a few minutes. If you’re using the utcNow function to get the time, then you can’t be sure that the Flow is at the start because it’s triggering at random intervals. To make more precise comparisons, you can remove that indetermination by using the startOfHour function to know that, even if your Flow fired a bit late, you always have the “correct” value to compare.
Depending on the size of your string, your expression may return an error, even if it’s correct. Please note that the expressions have a max size of 8,192 characters. If you have an expression that is even bigger than 1000, I would strongly advise that you break it into smaller, manageable formulas.
Here are some things to keep in mind.
Don’t use it to calculate the start of the day.
There’s a function to do that called startOfDay that can help you do that.
Use date functions as much as possible
Please use any function that deals with dates like addDays or formatDateTime, for example, instead of doing the manual calculations or trying to format the date yourself. Even if you want to display the date in your language, you can use existing functions to do it. I have an article on how to do it here. Dates are incredibly complex, and Power Automate has functions that remove the frictions of formatting a date, dealing with timezones, and dealing with regional settings, to name a few. It will save you headaches and be much more precise than trying to do the conversion yourself.
Don’t format the date with the startOfHour function.
I recommend always using the formatDateTime function to format your dates instead of doing it here. Sure, you can do it in one step, but the formatDateTime function provides you with a lot more flexibility when it comes to converting dates. Also, the formatDateTime function provides the locale that will allow you to translate things like the month, for example, into a language of your choosing.
There’s no real reason to do it, but if you find yourself in a situation where you have nested startOfHour functions in a formula, you should review it and make everything more straightforward. If you do it, you may end up in the same place since it will return to another timestamp at the start of the hour unless you don’t use the second parameter. If you use the format parameter, depending on the one you use, your expression can fail; please consider if you see nested startOfHour functions.
Always add a comment
Adding a comment will also help avoid mistakes. Please indicate why you are converting the date and why. It may look obvious initially but will not be in a few months or years. It’s essential to enable faster debugging when something goes wrong.
Microsoft’s startOfHour Function Reference
Back to the Power Automate Function Reference.
Photo by Tristan Colangelo on Unsplash