A set of the most commonly used expressions that you can use in your Flows.
Convert a relative to an absolute URL
If you have a reference in your HTML that is a relative URL, like, “/area/Microsoft/powerautomate/” you need to be converted to absolute; you can use the expression below to get “https://manueltgomes.com/area/microsoft/powerautomate/”
replace(<string with the relative url>,'href=''/','https://manueltgomes.com')
After this, you can use the new URL in your “href”.
Convert an email ID to an URL to the email
We will convert the identifier of the conversation from the “Office 365 When a new email arrives” trigger, for example, and create an URL to open the email directly.
The expression works for both shared and private mailboxes.
Format Date & Time
ISO 8601 (year, month, and day)
Here’s how to display a date in the ISO 8601 format. This format is usually used in Europe, and it shows the date as “year-month-day hour:minute:second”. Please note that the hour is in 24h format, and all of the values are always represented with two digits. Please replace your variable with the “utcNow()” in the following example:
Based on the user’s locale
If you want the date to show up consistently in the user’s locale, you can define it with the “d” or “D” format. Both Power Automate and Power Apps will display it the same way. In the following example, please replace your variable with the “utcNow().”
Saturday, March 13, 2021 (en-US)
Sábado, 13 de Março de 2021 (pt-PT)
Samstag, 15. März 2021 (de-DE)
Convert an Excel Number into a Date
Excel displays the dates as expected but stores them in a number format. This number is the elapsed days since the 1st of January 1900. You must first create that date and add the days to that date to convert it into a date—one note. You need to remove one day; otherwise, you’ll get the next day of the date. The reference date is the value “1” and not “0,” so adding those days will result in one extra day that we need to remove.
Note: I noticed recently that the date calculation shifted one value down again. After checking with Excel, the reason is apparent. Microsoft corrected the bug/feature that incorrectly classified 1900 as a leap year. Microsoft wrote about Excel, but apparently, they fixed it in Power Automate. So instead of one down, we have to decrease 2 to compensate for Excel’s bug/feature.
addDays(formatDateTime('1-1-1900','dd-MM-yyyy'),sub(<variable with the excel number>,2))
Convert a Date into an Excel Number
Excel stores the dates in a number format, so it may be helpful to convert them. It works in the opposite direction as the expression above, where we calculate the number of days elapsed since the reference date and return it as the number to be stored. We’re using the ticks function with a different reference date, hence the additions and subtractions that you see. I explain the formula below in detail if you want to understand each segment and its math.
add(div(sub(ticks(<INSERT HERE THE DATE TO CONVERT>),ticks('1900-01-01T00:00:00Z')),864000000000),1)
How to use the expressions
There’s no big trick in how to use them. You can copy and paste them into your Flows. If you need help or something is not working as expected, let me know. You can also check our articles on Microsoft Power Automate to learn more about the tool and the know-how to create your own. I encourage you to download the templates and adapt them to your needs. Don’t worry about attribution. It’s not necessary, but I appreciate it if you do.
Have a cool Expression?
Awesome, send it my way via email with a simple explanation of what it does so that I can include it here in this cookbook. I’ll attribute submitted templates, so please include any information you want to be displayed. I’ll test them first to ensure quality and don’t do anything strange. I’ll only post them in this expressions cookbook if I find something worthwhile that others can benefit from. Depending on my workload, it can also take me a while, but I promise to look at all of them. Please also submit only expressions you created and have permission to share. I cannot be considered responsible for any provided indication being proprietary. Don’t hesitate to contact me if you find any violation of the terms described below, and I’ll remove it immediately.
This cookbook’s expressions have been tested as of the date each was added to manueltgomes.com for compatibility with the then-current versions of Microsoft’s Power Automate. Please feel free to use these expressions and adapt them to fit your specific needs. However, please remember that Manuel T. Gomes and SKILLFUL SARDINE – UNIPESSOAL LDA. can not guarantee that this cookbook’s expressions will remain compatible with future updates. Moreover, the terms included in this expressions cookbook are provided free of charge and without express or implied warranties, including implied warranties of merchantability, fitness for a particular purpose, and non-infringement. The SKILLFUL SARDINE – UNIPESSOAL LDA. Makes no specific promises about the expressions, their specific functions, or their reliability, availability, or ability to meet your needs. Also, the SKILLFUL SARDINE – UNIPESSOAL LDA. will not be liable to you for any lost profits or other consequential, special, indirect, or incidental damages arising out of or connected with your use of any of the expressions in this cookbook.