cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dennisb88
Resolver III
Resolver III

Calculate minute difference between hard value and created time

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.

1 ACCEPTED SOLUTION

Accepted Solutions

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)

muralidharan_0-1620389332812.png

 

 

Output as

muralidharan_1-1620389440165.png

 

Hope this helps you and you will get some idea.

 

Thanks,

Murali

 

View solution in original post

9 REPLIES 9
HaydnTH
Frequent Visitor

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.

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?

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 !

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!

dennisb88
Resolver III
Resolver III

Anyone who know how to solve this?

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 

 

 

Hi @muralidharan ,

 

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

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)

muralidharan_0-1620389332812.png

 

 

Output as

muralidharan_1-1620389440165.png

 

Hope this helps you and you will get some idea.

 

Thanks,

Murali

 

View solution in original post

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!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (1,406)