How to Calculate the Date Difference using Power Automate

Hi, I came across a scenario in power automate where I had to calculate the date difference using power automate. I had to calculate number of days difference between todays date and when the item was created in a SharePoint list. I thought it can help other power automate developers in the community, hence made this video and uploaded to the YouTube channel.

Expression: In the below code, add the desired column to calculate the date difference dynamically inside last ticks method.

div(sub(ticks(formatDateTime(utcNow(),'yyyy-MM-dd')),ticks()),864000000000)

I am calculating the days difference, you can calculate hours, min, seconds and milliseconds difference by changing the divided by figure in above expression accordingly.

Ticks per day 864,000,000,000
Ticks per hour 36,000,000,000
Ticks per minute 600,000,000
Ticks per second 10,000,000
Ticks per millisecond 10,000

[Genkijapchin]

Finally a simple, concise explanation of how to do this! Thanks so much.

[CarlosSolisNow]

Thank you for this. Having the code in the comments made it so much better and easier to do. Greate work.

[HumaidahFulyani-liem]

Hi,
I got this error message, please advice
The variable 'varPRDaySinceSubmitted' of type 'Boolean' cannot be initialized or updated with value of type 'Integer'. The variable 'varPRDaySinceSubmitted' only supports values of types 'Boolean'.

[ramachandranjayaraman]

Explanation is very clear. I have different scenario - I have coupon issue column in share point (which is updated manually with the issued date) and when emp apply for again using Form, Power automation shld check whether the last coupon issued is greater than 6 months from the current applied date, if yes create an new entry and if no reject email.

[ManojKumar-zngf]

Hello, Thanks for the beautiful video! I need to get clarity for a question from you. I have an Excel file with a couple of columns, kept the file in SharePoint location, and with the help of list rows present in a table operation trying to read data from the Excel file with date format as "ISO 061", the output looks like "Joining Date": "2021-01-18T00:00:00.000Z" but I need only the date and month from above value Expected output - "01-18". How do I need to specify the condition in ''Enter custom Value" to get the desired output

[mirzatanjic]

Thank you. How to calculate duration between two dates in format Y M D ?

[jaytricegarrett]

Hello - I have a share point list that has a date column. I want to look at that date column and if any dates are more than 30 days from today I want it to update an item in my share point list.

[shanyinkai]

Great work! Now we got number of days for each single item, I tried to build a summary table and send out via email to user, however i am fail to add this number of days in it. Do you happen have any idea? Many thanks!

[EdVasquez-wf]

How do I set a condition where, if submission time is less than or equal to 3pm, it will trigger a different set of emails if yes or no?

[sumanpreetkaur]

How would I edit the formula if I already have due date and want to calculate between the due date and the submission date?

[gooli]

Thank you verymuch- i got a challange- Since i have many items in SP list- i prefer to get one email with HTML table along with calculation. how can we do it... thanks in advance.

[patriciaadams]

Very helpful, thank you!  I was able to do this and instead of an email as the last action, I created a new list and had the calculated days inserted in the list.  I do have a question.  After I calculated how many days since an item was created, I would like to calculate how many days since an item was approved.  Suggestions on how to do that?  Thank you!

[sunithajs]

what is the difference between get items and get item in the flow

[lifechanger]

Hello. Here is error like after typing It shows '16-55-2021 for provided tick value. Can i know how to fixed it

[jamunaranihs]

Hi, how to calculate difference between dates in two years, like 11/20/2021 and 1/18/2022

[Opikateur]

Hi, Thanks for this tuto :)
I've set my flow thanks to your video but I've found some differences between 2 dates. After checking your example from 17/08/2020 to 09/11/2020 I count 25 days but not 24. Also, my flow find 6 days between 22/04/2021 (thursday) and 29/04/2021 (thursday) instead of 7. Do you know how to fix this issue ?
Br,
Gaultier

[krishnabairi]

Really a good video.. I have been looking for this for a while now. Can we use the same expression if the source is excel?

[karukarthi]

Thanks for the video.. Really useful and one more query here is, we always need to compare with modified column rathen going with created column.. Else as you said, we can use any other columns which are date type format.

[gyanibaba]

Hi, can we send all record age in one email using Html table

[charliebaxter]

I keep getting an "enter a valid integer" error when I try to run this with data from planner instead of sharepoint. Any ideas?