Power Automate: startOfDay function

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 “startOfDay” function. This function will get any date and return a new date at the beginning of the day (usually midnight).

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.

1. Date
2. Format (optional)

Let’s start with a simple example, using the utcNow function.

startOfDay(utcNow())

will return 

2022-08-31T00:00:00.0000000Z

Notice that even if we’re using the utcNow function that will return the date with the time component, the startOfDay function will return the midnight of the day you defined.

You can also provide your timestamp as follows:

startOfDay('2022-08-31T01:32:02.0068700Z')

will return 

2022-08-31T00:00:00.0000000Z

The date will always be converted to the reference start of the day.

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 startOfDay 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:

startOfDay('2022-08-31T01:32:02.0068700Z','MMMM dd, yyyy')

will return 

August 31, 2022

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, of a SharePoint list that contains dates inserted by the users. If you’re performing calculations on the dates, the hour time is significant; then you need to reset the date to a reference date and time so that all results of your calculation are the same. Notice that the result is not the same as ignoring the hour time or truncating the date. Some systems default to noon when a date is not provided, not to midnight, meaning that if you’re performing calculations, they can be off as much as 12h.

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 you trying to do the conversion yourself.

Don’t format the date with the startOfDay 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 startOfDay 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 day unless you don’t use the second parameter. If you do use the format, depending on the one you use, your expression can even fail please consider if you see nested startOfDay 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 startOfDay Function Reference

Back to the Power Automate Function Reference.

Photo by Janaíne Arioli on Unsplash