by Power Automate interacts heavily with APIs, and these tend to store dates in a timestamp. Timestamps are fantastic since they remove all the regional settings issues and time zones since it’s always a reference from a fixed point in time.
If you’re not familiar with timestamps, they represent the number of seconds elapsed since the reference date of the 1st of January 1970 00:00:00. I know it looks arbitrary, but there’s an excellent Wired article explaining why this date. Please note that this is an extremely simplistic explanation, but I think it’s enough so that you can understand the concept and use timestamps in your Power Automate.
Note: You’ll see in Microsoft’s documentation description of timestamps as objects like ‘2019-10-28T10:10:10Z’. Intentionally I’m calling these Dates and keep the UNIX concept of the timestamp to avoid confusion.
The only issue is that Power Automate doesn’t have a clean way to convert a timestamp to date. For this, we need a formula that does the math for us.
Creating a test Power Automate
First, let’s create a simple Power Automate to test our formula (you can find the template below):
I’ll explain the formula in detail after, but let’s look at it first:
addseconds('1970-1-1', triggerBody()['number'],'yyyy-MM-dd hh:mm:ss')
If you want to get only the date, you can do the following:
addseconds('1970-1-1', triggerBody()['number'],'yyyy-MM-dd')
Just copy it into your expression, and you’re good to go:
Explaining the formula
The formula is quite simple and follows the definition of the timestamp closely. I’m adding the number of seconds, defined in,triggerBody()['number'] and adding the seconds, using the addsecondsformula to the reference date that is 1970-1-1. I get the value of the timestamp directly from the trigger of the Flow but, in your flows, you’ll probably get it from other fields. To use the formula, replace the triggerBody()['number'] with your field.
Finally, I present the information by defining the date format Year - Month - Day Hour:Minute:Second. You can set the one that suits you.
A small detail to take into consideration
You need to be sure that the format of the date defined is the same as the data source if you’re saving it. For example, if you set “dd-MM-YYYY” and the data source is expecting “MM-dd-YYYY,” you will get errors and invalid data collected.
Resources
- Example flow. If you don’t know how to import Flows, I have an article that can help you here.
- Current Timestamp Tool. Get the current timestamp to test your flow.
Have a suggestion of your own or disagree with something I said? Leave a comment or interact on Twitter and be sure to check out other Power Automate-related articles here.
Featured Image by Amanda Jones on Unsplash
