Issue with formula calculating hours since reported

(0) Share

Report

Posted on by jbrines

1,121

Hi All,

I got help on here with my flow to calculate hours and dated from when an it was reported (not created). It appears to be fine for days but the hours one is way off.

div(div(div(mul(sub(variables('ticksUTCNow'),variables('ticksCreated')),100),1000000000),60),60)

If the item was reported at 18/01/21 10:33 and I run the flow at 18/01/21 15:07 it tells me that it 14 hours but if I calculate the time it is 4h 35m

Could it be to do with the figure 1000000000

Categories:

Building flows

I have the same question (0)

All responses (8)

Answers (0)

fchopo 8,003 Moderator on at

Like (1)

Report
Hello @jbrines

You can have a look at this excellent post by @edgonzales : Get time difference in hour - Power Platform Community (microsoft.com)

Basically, we have to convert the date to ticks, and then convert it to minutes:

div(ticks(utcNow()),600000000)

I did a test on my side in the following way:

The output of the compose 3 action is the difference in hours between compose 1 and compose 2 dates. The expression used is:

div(sub(div(ticks(outputs('Compose_2')),600000000),div(ticks(outputs('Compose')),600000000)),60

Hope it helps!

Ferran

Was this reply helpful? Yes No
jbrines 1,121 on at

Like (0)

Report

@fchopo

Hi Ferran,

This is what I have, what would I need to change?

Was this reply helpful? Yes No
fchopo 8,003 Moderator on at

Like (1)

Report
Hello @jbrines

In the "Calculate difference in hours" action I would use this expression:

div(sub(div(ticks(utcNow()),600000000),div(ticks(coalesce(items('apply_to_each')?['ReportedDate']),utcNow()),600000000)),60)

Regards,

Ferran

Was this reply helpful? Yes No
jbrines 1,121 on at

Like (0)

Report
@fchopo It failed with this error

Unable to process template language expressions in action 'Compose_-_calculate_difference_in_hours' inputs at line '1' and column '19148': 'The template language function 'ticks' expects a single parameter that is a timestamp. The function was invoked with '2' parameter(s). Please see https://aka.ms/logicexpressions#ticks for usage details.'.

Was this reply helpful? Yes No
fchopo 8,003 Moderator on at

Like (0)

Report
Hello @jbrines

Probably I forgot a parenthesis... Maybe it's easier using the variables you created:

div(sub(div(ticks(variables('ticksUTCnow')),600000000),div(ticks(variables('ticksCreated')),600000000)),60)

Regards,

Ferran

Was this reply helpful? Yes No
jbrines 1,121 on at

Like (0)

Report
@fchopo See error below.

Unable to process template language expressions in action 'Compose_-_calculate_difference_in_hours' inputs at line '1' and column '19147': 'The template language function 'ticks' expects its parameter to be a string that represents a timestamp. The provided value is of type 'Integer'. Please see https://aka.ms/logicexpressions#ticks for usage details.'.

Was this reply helpful? Yes No
fchopo 8,003 Moderator on at

Like (0)

Report

Hello @jbrines

It's rather difficult to help you in this situation, since I don't see the outputs of the different actions when you execute the flow. Please, have a look at the formulas and apply them accordingly.

Regards,

Ferran

Was this reply helpful? Yes No
jbrines 1,121 on at

Like (0)

Report

@fchopo

Hi Ferran I have looked at the formula and searched the internet and this community and can't get it fixed.

Are you looking for the outputs from the flow after it has run?

John.

Was this reply helpful? Yes No