Calculate minute difference between hard value and created time

(0) Share

Report

Posted on by dennisb88

603

Hi all,

I've need to calculate the difference in minutes between a SharePoint created Time and the time of 6.00 at the same day.

For example when a SharePoint list item is created today at 05:45 I'm expect the 15 as result.

I'm trying to do it as follow:

div(sub(ticks(outputs('Get_item')?['body/Created']), ticks(formatDateTime(UTCnow,'MM-dd-YYYY')' 06:00', -600000000)

But that's not working/ give an expression error.

Does anyone know how to get the right results?

Thanks in advance.

Categories:

Building flows

I have the same question (0)

All responses (9)

Answers (1)

HaydnTH 9 on at

Like (0)

Report

Copying it out into Notepad++ it looks as follows;

div(
sub(
ticks(
outputs('Get_item')?['body/Created']),
ticks(formatDateTime(UTCnow,'MM-dd-YYYY')'
06:00', -600000000)

Looks like a couple of potential formatting issues;
namely, on the second line starting 'ticks' that should end with a , and not a '
and your Div and Sub controls aren't closed, each function should be closed with a ), so typically, if you're running 3 functions you may see ))) at the end of the Power Automate.

I can't speak for the functions themselves, but hopefully the syntax will get you somewhere.

Was this reply helpful? Yes No
dennisb88 603 on at

Like (0)

Report
Hi @HaydnTH ,

Thanks for reply.
You right about the closing functions. But also when I've fix it, it's told me that it's not correct:

div(sub(ticks(outputs('Get_item')?['body/Created'])), ticks(formatDateTime(UTCnow,'MM-dd-YYYY') 06:00), -600000000)
Maybe someone Else know a solution?

Was this reply helpful? Yes No
Muralidharan 340 on at

Like (1)

Report
Hi @dennisb88 ,
Seems to be some syntax error in the expression.
1. The UTCnow should be called as utcNow()
2. Subtract method closed incorrectly.
3. ticks method incorrectly formed.

ticks(formatDateTime(UTCnow,'MM-dd-YYYY') 06:00)

I've correct the syntax issues and apply in your flow and it should work without any error. Kindly double check the logic before applying into production environments.

div(sub(ticks(outputs('Get_item')?['body/Created']), ticks(addHours(formatDateTime(utcNow(),'MM-dd-yyyy'),6))), -600000000)

Hope this helps !

Was this reply helpful? Yes No
dennisb88 603 on at

Like (0)

Report

Hi @muralidharan ,

Thank you for your input! That's helping me in the right direction.
But it's not exactly what I need, maybe my question wasn't full complete:

What I need to calculate is the time difference between the created time and 06:00 AM in the morning.
So for example, today (5/5/2021) at 05:50 the item is created. The result I expect from the expression should be 10.

Do you know how achieve that?

Thanks advance!

Was this reply helpful? Yes No
dennisb88 603 on at

Like (0)

Report

Anyone who know how to solve this?

Was this reply helpful? Yes No
Muralidharan 340 on at

Like (0)

Report

What in case, if the item is created at 6.10, then difference is -10 mins ? Do you want to just the time difference ?
Item Created : 5.50 -> 10 mins difference
Item Created at 6.10 -> 10 mins difference

Was this reply helpful? Yes No
dennisb88 603 on at

Like (0)

Report

Hi @muralidharan ,

Yes in case it's created at 6.10 it should be have the value -10

Was this reply helpful? Yes No
Verified answer

Muralidharan 340 on at

Like (0)

Report

Hi @dennisb88 ,
Can you please try the following ?
1. CreatedDate Time expression - I'm keeping it as 6 am :
addHours(formatDateTime(utcNow(),'MM-dd-yyyy'),6)
2. Taking current time, utcNow() used.
3. Subtract and convert into Minutes
div(div(mul(sub(ticks(outputs('Created_DateTime')),ticks(outputs('Compose'))) ,100),1000000000),60)

Output as

Hope this helps you and you will get some idea.

Thanks,
Murali

Was this reply helpful? Yes No
dennisb88 603 on at

Like (0)

Report

Thanks! That's helping! Now I only need to convert it to the correct timezone and I'm done! (I'm in timezone Amsterdam, so I've a difference about 2 hours.)

Thanks a lot!

Was this reply helpful? Yes No