This Expressions Cookbook contains some of the expressions that I find useful. I’ll add more over time, so don’t be discouraged if you only see a few.
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 bellow 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”.
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 displays 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 2 digits. In the following example, please replace your variable with the “utcNow().”
Based on the user’s locale
If you want the date to show up always 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 we would expect but store them in a number format. This number is the elapsed days since the 1st of January 1900. To convert into a date, you must first create that date and then add the days to that 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 this regarding 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 useful 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. Please note that 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 the math behind it.
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 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 that they don’t do anything strange. I’ll only post them in this expressions cookbook if I find something worthwhile that others can benefit from. It can also take me a while, depending on the workload that I have, but I promise to look at all of them. Please also submit only expressions that you created and you have permission to share. I cannot be considered responsible for any provided expression 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 his team can not guarantee that this cookbook’s expressions will remain compatible with future updates. Moreover, the expressions included in this expressions cookbook are provided free of charge and as-is without any express or implied warranties, including implied warranties of merchantability, fitness for a particular purpose, and non-infringement. The Manuel T. Gomes Team makes no specific promises about the expressions, their particular functions, or their reliability, availability, or ability to meet your needs. Also, the Manuel T. Gomes Team 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.
Photo by Micheile Henderson on Unsplash