cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dennisb88
Helper V
Helper V

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
Helper V
Helper V

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
MPA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors
Users online (1,329)