*“I’ve got two times, the task start time and end time, how do I calculate a difference in minutes or hours with Power Automate?”*

One of the features missing in Power Automate is a calculation of a difference between two dates / times. There’re many operations with date, you can calculate a date, you can format a date, but you can’t easily calculate a difference. To do that you’ll need a combination of expressions, and that’s what this post is about.

## Calculate the number of ticks

As already explained in the post on Excel based reminders, Power Automate has an expression called ticks(…). This expression will take a date and calculate a number of ticks for that date. One tick is 100 nanoseconds, starting with 1st of January 0001 00:00:00. For example, 6th of February 2020 at 19:28 will return 637797689122217300 ticks.

When you calculate a difference between two dates / times, you must get ticks for both of the dates, e.g.

```
today - yesterday
ticks(today) - ticks(yesterday)
ticks(utcNow()) - ticks(addDays(utcNow(),-1))
```

Once you have the two numbers of ticks, you can subtract one from the other with the sub(…) expression.

```
sub(ticks(utcNow()),ticks(addDays(utcNow(),-1)))
= 864000000000
```

**Note**: if you end up with a negative number, you should switch the two dates in the expression.

## Convert ticks into more readable unit

The result of the subtraction should be then converted into a more understandable number. You don’t want to show users the difference in billions. Use the div(…) expression to divide the number by a number representing the desired unit.

Divide by | To get |
---|---|

864000000000 | days |

36000000000 | hours |

600000000 | minutes |

10000000 | seconds |

For example, to get the number of minutes between yesterday and today:

`div(sub(ticks(utcNow()),ticks(addDays(utcNow(),-1))),600000000)`

## Summary

To calculate a difference between two dates / times in Power Automate, you must combine multiple expressions. Calculate the number of ticks for each date, and then divide it by a number to get the desired unit. Start from 100 nanoseconds (1 tick) and divide it by a number big enough to get the days/hours/minutes.

And if you’d like to get the result as a decimal number, apply float(…) on one of the numbers.

Tom,

on a similar topic I am trying to covert an Excel Time only column to Sharepoint time only column with power automate.

I can do this for a date only using technique described here

https://normyoung.ca/2020/07/20/import-excel-data-into-an-existing-sharepoint-list-using-power-automate/

Can you suggest how to modify this for a time only column

Thanks

Steve

Figured it out

formatDateTime(variables(‘varStartTime’),’HH:mm tt’)

Hello Steve,

I’m glad that you solved it, thank you for sharing the solution.

Nice write up Tom, solved my problem right away.

Power Automate actually does have a “dateDifference” function under expressions. But it returns it in time format. Ex: 00:01:57.8418729

Hello Iron,

I noticed that there’s this new expressions, but I don’t like the result format. I’d much prefer if they allowed you to enter a 3rd parameter with a unit, e.g. dateDifferece(…,…, ‘seconds’)

How to calculate the difference between two dates excluding the weekend

Hello Rachayeeta,

I don’t know, I never needed that.

Gostaria de criar um alerta no planner atraves da automatização do power automate, porém tenho a data oficial que é a entrega da tarefa, mas queria que criasse um alerta que antecede 3 dias a data oficial, teria como?