by 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 “startOfMonth” function. This function will get any date and return a new one at the beginning of the month.
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.
Usage
It follows a simple pattern.
- Date
- Format (optional)
Let’s start with a simple example, using the utcNow function.
startOfMonth(utcNow())
will return
2022-08-01T00:00:00.0000000Z
You can also provide your timestamp as follows:
startOfMonth('2022-09-07T01:32:02.0068700Z')
will return
2022-09-01T00:00:00.0000000Z
The date will always be converted to the reference start of the month.
Notice that we’re converting the time as well to the reference start of the day (midnight).
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 startOfMonth 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:
startOfMonth('2022-08-31T01:32:02.0068700Z','MMMM dd, yyyy HH:mm')
will return
August 01, 2022 00: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 and don’t have to do complex calculations. For example, let’s think that you have a Flow that needs to know how many days have passed since the start of the month. You can try to parse the date to replace the day with the first day of the month, but that would be cumbersome. By using the startOfMonth function, you can get the result easily and then proceed with the comparison of the dates. It’s all about reducing complexity and mistakes and if you’re dealing with dates there will be always mistakes, believe me.
In case you don’t know how to calculate the difference between two dates, I have an article that can help you here.
Limitations
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.
Recommendations:
Here are some things to keep in mind.
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 startOfMonth 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.
Don’t nest
There’s no real reason to do it, but if you find yourself in a situation where you have nested startOfMonth 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 month 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 startOfMonth 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.
Sources:
Microsoft’s startOfMonth Function Reference
Back to the Power Automate Function Reference.
Photo by Nelly Antoniadou on Unsplash
