#PowerPlatformTip 167: 'Date Math Without a dateDiff() - the ticks() Trick'

TL;DR: There is no function named dateDiff() in Cloud Flow expressions (the closest built-in is dateDifference(), which returns a timespan string, not a number). Convert both timestamps with ticks(), subtract them, and divide by a fixed constant to get the difference in days, hours, minutes or seconds. Bonus: comparing dates with ticks() is far safer than comparing formatted strings.

You need the number of days between a due date and today - a two-line calculation in almost any language. In a Cloud Flow you reach for the expression editor and… there is no dateDiff(). There is a dateDifference(), but it returns a timespan string, which is awkward to compare or calculate with. So people start slicing strings, comparing text, and getting subtly wrong results the moment a format or a time zone shifts. There is a clean, reliable primitive hiding in plain sight: ticks().

💡 Challenge

Power Automate’s Workflow Definition Language has functions to add time (addDays, addHours) and to format time (formatDateTime), but no function to subtract two dates and return a plain number. dateDifference(startDate, endDate) exists and returns a timespan string in the form days.hours:minutes:seconds, which you would still have to parse. Comparing dates as strings works only by luck (and only for a strict YYYY-MM-DD layout), and it breaks as soon as time components, offsets, or locales enter the picture.

✅ Solution

Use ticks(). A tick is a 100-nanosecond interval, and ticks('<timestamp>') returns the number of ticks since January 1, 0001, 00:00:00 up to that timestamp, as a big integer. Because it collapses any timestamp to one comparable number, you can subtract two ticks to get an exact duration - and divide by a fixed constant to convert that duration into days, hours, minutes or seconds. The same number also gives you a bullet-proof date comparison.

🔧 How It’s Done

1. Difference between two dates (in days).

🔸 Subtract the two tick values and divide by the number of ticks in one day (864000000000):

div(sub(ticks(outputs('DueDate')), ticks(utcNow())), 864000000000)

🔸 The result is a whole number of days. Positive means the due date is in the future, negative means it’s overdue.

2. Pick your unit with the right constant.

🔸 Keep the div(sub(ticks(a), ticks(b)), <constant>) shape and just swap the divisor:

🔸 Days = 864000000000, Hours = 36000000000, Minutes = 600000000, Seconds = 10000000.

3. Reliable date comparison.

🔸 Don’t compare formatted strings. Compare ticks instead:

greater(ticks(outputs('DateA')), ticks(outputs('DateB')))

🔸 This is a numeric comparison, so it is immune to formatting and time-zone surprises. Use it in a Condition or a Filter array to check “is A after B?”.

Real flow run: a 5-action chain (Due Date → Today → Ticks Due Date → Ticks Today → Days Difference) resolves div(sub(ticks(...), ticks(...)), 864000000000) to an exact 7 days.

Real flow run: Code View confirms the actual expression behind that result, subtracting two ticks() outputs and dividing by the fixed day constant.

4. Build a sortable timestamp.

🔸 Need a value that always sorts chronologically (e.g. as a SharePoint column or a file-name prefix)? ticks(utcNow()) gives you a single monotonic integer - no locale, no separators, no ambiguity.

🎉 Result

You get exact date differences in whatever unit you need, date comparisons that never lie, and a clean sortable timestamp - all from one tiny function, with no premium connector.

🌟 Key Advantages

🔸 A plain number: gives you the difference as a number, which dateDifference() does not.

🔸 Exact and unit-flexible: one pattern, four constants - days, hours, minutes, seconds.

🔸 Comparison you can trust: numeric ticks() comparison beats fragile string comparison.

🔸 Sortable by design: ticks(utcNow()) is a monotonic integer, perfect for ordering.

🛠️ FAQ

1. Why not just compare the date strings?

String comparison only happens to work for a strict YYYY-MM-DD layout with identical time zones. Add a time component, an offset, or a different format and the ordering breaks. ticks() turns every timestamp into one comparable number, so the comparison is always correct.

2. Can I use ticksToDays() / ticksToHours() / ticksToMinutes()?

Not in Cloud Flows. Those helpers exist only in Adaptive Expressions (Copilot Studio / Bot Framework), not in the Workflow Definition Language used by Power Automate cloud flows. In a flow, divide by the fixed constant yourself (see step 2).

3. Where do the magic numbers come from?

A tick is 100 ns, so one second is 10,000,000 ticks. Multiply up: minute 600,000,000, hour 36,000,000,000, day 864,000,000,000. Keep them as plain integers in the expression.

4. When is dateDifference() the better choice?

When you want to show a duration rather than calculate with it. dateDifference('2015-02-08', '2018-07-30') returns the timespan string "1268.00:00:00" (days.hours:minutes:seconds), and the result is negative if the start date is later than the end date. For conditions, sorting and arithmetic, the numeric ticks() pattern is easier.

5. My difference is off by a day - why?

ticks() respects the time portion, so a difference of 23 hours divides down to 0 days, not 1. If you only care about calendar days, normalise both inputs first (e.g. formatDateTime(x, 'yyyy-MM-dd')) before taking ticks() - and remember MM is month while mm is minute.

🔗 Related Tips

• #PowerPlatformTip 6: formatDateTime in utcNow, format a timestamp directly - and the classic MM vs mm gotcha.
• #PowerPlatformTip 3: Advanced Filtering Array, where a ticks() comparison shines inside a Filter array.
• #PowerPlatformTip 11: Trigger Condition, drop a ticks() comparison straight into a trigger condition.