← Al Davies, 250 High Street

How to calculate the days between two dates in Power Automate

Use div(sub(ticks(endDate), ticks(startDate)), 864000000000) to get the days between two dates. The newer dateDifference() function also works, but it returns text like 5.02:00:00, not a number.

What is the simplest expression for days between two dates?

Add a Compose action and paste this expression. Replace the two inner parts with your own dates: div(sub(ticks(utcNow()), ticks(triggerOutputs()?['body/DueDate'])), 864000000000).

A tick is 100 nanoseconds. One day holds 864000000000 ticks, so the division turns the gap into days. Both numbers are whole, so the result is whole days. Use 864000000000.0 as the divisor if you want decimals, such as 4.5 days.

How do you get days with decimals?

  1. Add .0 to the divisor to keep the decimals: div(sub(ticks(end), ticks(start)), 864000000000.0).
  2. Use the same pattern with a different divisor for other units. Hours use 36000000000. Minutes use 600000000.
  3. Put the result in a Compose action first. Check it in run history before you use it in a Condition.

How does the dateDifference() function work?

dateDifference(startDate, endDate) returns a time span as text. Microsoft's example returns 1268.00:00:00, which is 1268 days. For example, 5.02:00:00 means five days and two hours.

To read the day count, use split(dateDifference(start, end), '.')[0]. That works only when the gap is one day or more. A gap under one day has no day part, so the split returns the time text instead of a day number.

Which method should you choose?

  1. Use ticks() when you need a number you can compare, such as greater than 30 days.
  2. Use dateDifference() when you want a readable span, such as 5.02:00:00, for an email.
  3. Use ticks() when the gap can be under one day. It handles that case without extra checks.

Why is the result negative?

The end date is earlier than the start date. Swap the two values, or use mul(result, -1) to flip the sign.

For a due date check, use the due date as the end value and utcNow() as the start. A negative result then means the item is overdue.

Why does the expression fail or return an empty value?

  1. A date column is empty. Wrap the value in coalesce() and give it a fallback date. See our guide on Condition field is empty.
  2. The date is text in a format Power Automate cannot read. Convert it first. See our guide on formatDateTime and time zones.
  3. The two dates use different time zones. A SharePoint date-only column can shift by a day. Convert both values to the same zone first.
  4. The error says InvalidTemplate. Check the quotes and brackets. See our guide on InvalidTemplate errors.

What if you need business days instead of calendar days?

The ticks method counts every day, including weekends. Power Automate has no built-in function for business days.

To add working days to a date, see our guide on adding business days to a date. To run a flow on weekdays only, see Recurrence on weekdays.

FAQ

Does Power Automate have a datediff function?

Yes. dateDifference() exists, but it returns text such as 5.02:00:00. For a number, use ticks().

What is 864000000000?

It is the number of ticks in one day. One tick is 100 nanoseconds.

Can I get the difference in hours?

Yes. Divide the tick difference by 36000000000 for hours or 600000000 for minutes.

Sources