How to Calculate the Date Difference using Power Automate
HTML-код
- Опубликовано: 9 фев 2025
- 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 RUclips 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
Finally a simple, concise explanation of how to do this! Thanks so much.
Glad you liked it!
Thank you for this. Having the code in the comments made it so much better and easier to do. Greate work.
You're welcome Carlos!
Thank you very much for this video. It's brilliant. It has really helped me a lot!
I'm so glad to hear that you found the video helpful! Your support means a lot to me!
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.
thanks. did you resolve the issue?
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
Thank you soo much🖤
You're welcome!
Useful video. Thank you
You are welcome
Thank you. How to calculate duration between two dates in format Y M D ?
If I remember correctly, this video do calculate the date difference in YYMMDD format.
Thank you!!
You're welcome!
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?
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'.
This is because the varPRDaySinceSubmitted variable is of type Boolean, change it to Integer type in the variable initialization action
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.
Write a schedule flow, use, get items action in flow with filter addDays(utcNow(), -30, 'dd-mm-yyyy') , you will only get the records more than a month old. You can then loop through them and update items.
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.
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?
Never tried with excel but yes you can
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!
Hi Patricia, I would create a approval datetime column in the list. In the approval flow I would update this approval date column with flow approval time. Once you have the approval date in the list, you can use same logic in the flow to calculate the today's and approval date difference. I hope this helps you.
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!
You need to build HTML table using power automate.
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.
Hi Karu, glad to know that you liked it. Yes, this should work for any value of date type
How would I edit the formula if I already have due date and want to calculate between the due date and the submission date?
Just follow this video and replace the date columns accordingly. You can use any column names as long as they are in date time format.
@@KeaPointTechTips Thanks so much!!
Niceeeee! Ticks for months? Number of months between dates, ty.
Once you get number of days, you can divide by 30 to get number of months.
Hello. Here is error like after typing "846000000000". It shows '16-55-2021 for provided tick value. Can i know how to fixed it
Are the both dates in correct format?
Hi, how to calculate difference between dates in two years, like 11/20/2021 and 1/18/2022
The formula in the video work with dates in different years. Give it a try.
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
This example tell you the days gab between 2 dates without including the start and end dates. You can always add a day to end date in the formula to include it for the calculation.
@@KeaPointTechTips thanks for your answer. I'll do that :)
@@KeaPointTechTips What would the expression look like to add 1 day onto the formula above? Thanks
what is the difference between get items and get item in the flow
Get Items retrieve all items from a list, whereas get item will get you single item based on specified item id. I hope it make sense.
I keep getting an "enter a valid integer" error when I try to run this with data from planner instead of sharepoint. Any ideas?
Hi Charlie, are you trying to compare dates from Planner?
@@KeaPointTechTips Yes
I've resolved this now, I was using the wrong value from planner
Glad to know that you were able to resolve it.
Hi, can we send all record age in one email using Html table
Yes, you can, you need to loop through the source, calculate days different and log each item in a Json object, you can then parse Json, create HTML table and email the table.
How to Calculate Time when status column changes from Pending to Approved
You can save timestamp in the list when a column is changed and then use this tutorial to calculate difference between two dates.
How many ticks for hours?
It is listed in the video description
Ticks per hour 36,000,000,000