My most recent Power Automate builds have included a lot of work with date and time data. I thought this would be an excellent addition to the Getting Started with Power Automate series I started a few months ago. This is also a great starting place to learn about expressions. Read on to learn more about four functions that will help simplify working with date and time in Power Automate.
The most recent flow I built using date and time function helps me automate an expense submission. Each month, on the 26th, I submit an expense reimbursement for my cell phone. So, I build a scheduled cloud flow to run every day, and if today equals the 26th, kick off the process to submit the expense.
We will use the following functions….
In order to perform the following tasks…
- Get the current date and time
- Return the month and year of today
- Create a new date with today’s month and year and the day the expense should be submitted
Next week, I’ll share another flow that goes one step further to get the common name of the current month and day of the week (ex. Monday, Tuesday, January, February).
Users write expressions in Power Automate using the Data Operation category of actions, more specifically the Compose action.
If you are new to Compose actions, understand that you do not type out the expression inside the text box. Instead, click inside the text box to bring up another menu. Then, click the Expression tab. Now, you can build an expression. Also, always make sure to click OK or Update after entering the expression. Power Automate does not auto-save expressions.
If you are new to expressions, you might also be new to variables. Variables in Power Automate flows are placeholders or containers that you add content to. Variable is the category of actions, and when working with variables, you generally start with the Initialize variable action.
Several different types of variables are available. Next week, I’ll explain Array variables. This flow uses a string variable. Note, even though I am working with a number, I create the variables as string, not an integer for reasons that will be explained below.
In this case, my string is the day of the month that I need to submit the expense.
Getting the Current Time
First, Power Automate provides 2 ways to get the current date and time. Use either a Compose action with the utcNow() function or the Current time action. In my opinion, the Current time action is the easiest. The Current time action will provide both the date and time.
From here, I recommend converting to your local time zone using the convertFromUTC function. I wrote a post last week on UTC and how to convert to your local time zone and why you so do that. So, check that out.
Breaking Apart Dates
In Power Automate, the formatDateTime function does many things. In other applications, I have historically used different functions for extracting part of a date versus creating a date or formatting a date. In Power Automate, you may extract or format any part of a date time using the formatDateTime function.
For my use case, I want to get the month and year from the current date. Then, I will build a new date that is my payment date (the 26th of the month), so that I can use the condition ‘If today = payment date’. The formatDateTime function allows us to get the month and year, in separate actions. You can see the full expressions below.
Two pieces of syntax are required, the date and the format. Enclose the format in single quotes. You can find a list of the standard date and time format strings here.
And here are the outputs of the expression. It’s important to note, both outputs are strings. If the output was an integer it would be a different color, and there would not be a zero in front of the number 9.
Stringing Together A Date
So now, I need to create the payment date, which will be the 26th of each month. I create this date with both the concat function and the formatDateTime function. The syntax of the concat function is simply strings wrapped in single quotes, separated by commas. Note, the concat function will not work with numeric data types. This is why I created the payment date variable as a string rather than an integer. Then, I must also use formatDateTime to get this date into the same format as my current date.
Step 1 - create concat, add in the dashes to create the date concat(outputs('Current_month'), '-', variables('paymentDate'), '-', outputs('Current_year')) Step 2 - wrap in formatDateTime formatDateTime(concat(outputs('Current_month'), '-', variables('paymentDate'), '-', outputs('Current_year')), 'MM-dd-yy')
Now, before I wrap this up, you might be wondering why I went through all that trouble of concatenating the date. Why didn’t I just make everything an integer and leave the concat function out of it? Well, I didn’t do that because it doesn’t work. That generates an error because formatDateTime looks for a date or a string, not an integer.
Whew, and there we have it. Now you should know a little bit more about using variables and expressions, how to work with date and time in Power Automate.
Pingback: Make Finding the Name of Month/Day in Power Automate Easy! » The Analytics Corner
Pingback: How to Trigger Power Automate Flows From Teams » The Analytics Corner
Pingback: What Are Power Automate Solutions? » The Analytics Corner