i have date columns in sharepoint list
i want to set difference between two dates in variable
Solved! Go to Solution.
Hi @Tejaswini1234 ,
Could you please share a bit more about your scenario?
Do you want to calculate the difference between two date column values using Microsoft Flow?
Currently, within Microsoft Flow, there is no direct way to calculate the difference between two different date values.
As an alternative solution, I think the ticks() function within Microsoft Flow WDL expression could achieve your needs. I have made a test on my side, please take a try with the following workaround:
Add a "Initialize variable" action to initialize a variable to store the date difference.
Within the "StartTimeTickValue" action ("Compose" action), Inputs field set to following:
ticks(triggerBody()?['StartTime'])
Within the "EndTimeTickValue" action ("Compose" action), Inputs field set to following:
ticks(triggerBody()?['EndTime'])
Within the "DifferenceAsMinutes" action ("Compose" action), Inputs field set to following:
div(div(mul(sub(outputs('EndTimeTickValue'),outputs('StartTimeTickValue')),100),1000000000),60)
Within the "Set variable" action, Value field set to Output dynamic content of the above "DifferenceAsMinutes" action.
The "StartTime" and "EndTime" are both date time type columns in my SP list. The passed "StartTime" value and "EndTime" value as below:
The flow executes successfully as below:
If you want to calculate the Hours difference between two different date values, please consider go to the "DifferenceAsMinutes" action, then modify the Inputs field value to following:
div(div(mul(sub(outputs('EndTimeTickValue'),outputs('StartTimeTickValue')),100),1000000000), 3600)
If you want to calculate the Hours difference between two different date values, please modify above formula as below:
div(div(mul(sub(outputs('EndTimeTickValue'),outputs('StartTimeTickValue')),100),1000000000), 86400)
More details about using expression in flow actions, please check the following blog:
https://flow.microsoft.com/en-us/blog/use-expressions-in-actions/
In addition, you could also consider achieve your needs using the DateDiff() function within PowerApps. More details about the DateDiff() function in PowerApps, please check the following article:
Best regards,
Hi @Tejaswini1234 ,
Could you please share a bit more about your scenario?
Do you want to calculate the difference between two date column values using Microsoft Flow?
Currently, within Microsoft Flow, there is no direct way to calculate the difference between two different date values.
As an alternative solution, I think the ticks() function within Microsoft Flow WDL expression could achieve your needs. I have made a test on my side, please take a try with the following workaround:
Add a "Initialize variable" action to initialize a variable to store the date difference.
Within the "StartTimeTickValue" action ("Compose" action), Inputs field set to following:
ticks(triggerBody()?['StartTime'])
Within the "EndTimeTickValue" action ("Compose" action), Inputs field set to following:
ticks(triggerBody()?['EndTime'])
Within the "DifferenceAsMinutes" action ("Compose" action), Inputs field set to following:
div(div(mul(sub(outputs('EndTimeTickValue'),outputs('StartTimeTickValue')),100),1000000000),60)
Within the "Set variable" action, Value field set to Output dynamic content of the above "DifferenceAsMinutes" action.
The "StartTime" and "EndTime" are both date time type columns in my SP list. The passed "StartTime" value and "EndTime" value as below:
The flow executes successfully as below:
If you want to calculate the Hours difference between two different date values, please consider go to the "DifferenceAsMinutes" action, then modify the Inputs field value to following:
div(div(mul(sub(outputs('EndTimeTickValue'),outputs('StartTimeTickValue')),100),1000000000), 3600)
If you want to calculate the Hours difference between two different date values, please modify above formula as below:
div(div(mul(sub(outputs('EndTimeTickValue'),outputs('StartTimeTickValue')),100),1000000000), 86400)
More details about using expression in flow actions, please check the following blog:
https://flow.microsoft.com/en-us/blog/use-expressions-in-actions/
In addition, you could also consider achieve your needs using the DateDiff() function within PowerApps. More details about the DateDiff() function in PowerApps, please check the following article:
Best regards,
Thanks @v-xida-msft for this solution, it worked just fine!
I think there is a small mistake in your post. The following formula calculates the difference in days (not in hours) if I'm not mistaken:
div(div(mul(sub(outputs('EndTimeTickValue'),outputs('StartTimeTickValue')),100),1000000000), 86400)
Best regards
Sebastian
In spite a bit annoyed and elaborated, and surprised that Power Automate doesn't have direct functions to perform simple Date differences, this workaround worked well for me.
Thanks,
Abhijith
Hi @v-xida-msft,
What if the flow is scheduled one?
Does below works in that case?
triggerBody()?['StartTime']
As i have a scheduled job running every 6 hours, i have added your above solution but its giving me error.
Any suggestions?
Thanks,
Simmi
Hi All,
Below is the expression to get time difference between two timestamps in minutes.
div(sub(outputs('Compose_EndTime'),outputs('Compose_StartTime')),600000000)
If this helps, Mark it as accepted.
Thanks,
Simmi
User | Count |
---|---|
252 | |
106 | |
88 | |
51 | |
44 |