Calculate Time Difference Between Two DateTime In Power Automate

In this article, I will provide information on how to calculate the time difference between two given datetimes using Power Automate.

 

Below are the components used in this document:

1. Power Apps
2. Power Automate

Introduction

 

Many times we face a situation while designing a Power App, where we have to calculate a time difference. In my example, if the user is  logged in to the system, then the system should not ask user to provide credentials again after 30 minutes. Below is the step by step way to calculate whether it is been 30 minutes since the user logged in to the system. You can use the same logic to calculate time differences for your requirements.

 

Open Power Automate and create a new Flow

 

NOTE

In case you already have a Power Automate created, you can ignore this step.

 

Step 1

 

Browse to here and provide your Office 365 account details to login

 

 

Step 2

 

Select either a “Template” to start your Flow or you can search for the template. For my example I am selecting “Templates” from left navigation highlighted with yellow in the below image and then selecting Power Apps button, as the trigger point for my Flow is a button click from Power Apps. You can select the template based on your requirements.

 

 

Step 3

 

Empty Flow will be opened.

 

 

Power Automate To Calculate Time Difference

 

Step 1

 

In order to get the first dateTime value, in my example I am querying Azure SQL and getting whether the existing value of the user is active or not. Add a new Step “Execute a query” under SQL category. You can get this first dateTime value as per your requirement.

 

 

Step 2

 

Now to get the second dateTime, we take current UTC date. Add a new Step, “Compose” and provide input as below in “Expression”. You can get a second dateTime as per your requirement and change it to UTC format.

 

formatDateTime(addHours(utcNow(),5),'yyyy-MM-ddTHH:mm:ss')

 

 

Step 3

 

Now the logic to calculate the time difference is to have both dateTime in the same format and have “Ticks” calculated for them. Ticks is a 100 nanoseconds interval since 1 January 1601 00:00:00 UT of a string timestamp. Add a new Step “Compose” and put the below formula in “Expressions”

 

ticks(outputs('Compose'))

 

 

Step 4

 

Repeat the above step 3 to get the ticks for the SQL time (First dateTime) which we retrieved. Add a new Step “Compose” and put the below formula in “Expressions”.

 

ticks(body('GetValue')?['resultsets']?['Table1'][0]?['LastLoginDateTime'])

 

 

Step 5

 

Calculate the time difference now. Add a new Step “Compose” and put below formula in “Expressions”

 

div(div(mul(sub(outputs('GetCurrentTime'),outputs('SQLTime')),100),1000000000),60)

• Here “GetCurrentTime” is the second dateTime we retieved. In my example it is current UTC datetime
• Here “SQLTime” is the first dateTime. In my example we retieved it from SQL
• “1000000000” is to convert nanoseconds to millisecs.
• “60” to convert to mins
• If we need to calculate difference in hours, then we need to divide the final number one more time by 24

 

Step 6

 

Save your Flow and test the flow, we should see the time difference in minutes.

 

 

That is it. I hope you have learned something new from this article and will utilize this in your work.