How to Calculate the Date Difference using Power Automate

hello folks welcome back to  today's video I will show you   how to calculate the difference between  two dates in power automated workflows   so stay tuned and let's jump into  the demo after this short intro  i'm on a sharepoint training site i have expenses  list here so the list have a column created   so when this expense was actually submitted  so you can see the two entries there one is   17th of august this year next one is three minutes  ago which means recently created so what i want is   i want to create a power automatic workflow which  will take the today's date minus whenever the   expense was created and calculate the difference  so I'm expecting this to be returned 0 which means   that it is only created today so there is no  date difference and this I'm expecting around   24 days (about 3 and a half weeks) difference so  to create this workflow i need to go to office.com   by the way today dates is 11th of September that's  why i said 17th of august and 11 of September will   be around 24 days (about 3 and a half weeks)  difference so if i go to office.com and from   office 365 portal i can click on the ellipses on  the left hand corner and click on power automate   application icon this will take me to the power  automate application landing page from the left   hand menu i can click on create now for this  demo purpose i will just create an instant   workflow which i can just trigger myself and test  it I'll click instant workflow i will give this   workflow a name i will call it calculated which is  different manually trigger flow this will create   a first step for me next step i want is i want to  go through all the records in this sharepoint list   so i will click on next step and i will search for  get items action of sharepoint i will select the   site where this list exists the training site and  the list name we know this is expenses list and   that's it click save so we're done with that step  now we got all the items we need to then apply for   each loop to navigate through each item in this  list so next step we'll select apply to each   control of power automate so this will be input  from the previous step which is the value from   the get item value list of item and then the next  section we want to get item so we will select get   item this time the site address will be again the  same site and the list will be the same expenses   list the only difference is when you provide the  id so we can retrieve an individual item from that   list so we will get the id from this previous  step so we'll select id column here so we can   get the id dynamically now we need to calculate  the date difference from today's date and minus   the date when the expense was created so let me  just go back and create a variable here so i will   click on add an action i forgot to create that the  first place i will initialize variable i will call   this variable days since expense submitted so how  many days since the expense is submitted the time   will be integer now if i come back to my for each  loop under this cat item i will set this variable   add an action i will click set variable action of  power automate i will select the variable which is   days since expense submitted and the value now  this is an important one to understand now if you   see it is a big complicated expression here so  let's take one by one div mean with dividing at   the end sub mean we are subtracting ticks so one  tick is 100 nanosecond unit is called a tick now   this is we're formatting utc now is today's date  and then we are putting in this format that's why   we formatting it and when we call it ticks and  this today's date in formatted way which means   these are the number of ticks since 1st of Jan  1601 until today and then when we take this next   stick this is number of ticks we're using the  created by column so number of ticks since 1st   of Jan 1601 till the date it was created and we  are subtracting these two ticks so from today's   date minus date created so we subtract these two  ticks and the next thing we need to do is whatever   the number comes up we are dividing that number  by number of ticks in the whole day so that's the   number here you can have any different columns  you want from shape and list to have this state   and this state so for example if i just remove  this one and i will take this expression like   this i will put in the comment section below this  expression so you can copy and paste in your power   automatic workflow so if i copy this one and if  i go to value and expression and if i paste this   here and where i added the created column i will  select that location go back to dynamic content   and you can select any column you wish i  will select created again so you can see   that the created by column is added so it will  calculate the difference between today's date   and when that expense was created so if i click  ok that's done next thing we can do is we can   just send an email to see if he's calculating  the difference correctly for each record so   click to add an action send an email notification  that's the action we want i will just send it to   myself and the subject i will put the expense and  then title of the expense and i'll just say days   since created yeah and then in the body i will say  title and i will select title and then i will say   day since created we will select the variable days  since expense submitted okay so we are sending an   email for each record in sharepoint list to see  how many days it's been since the expense is   created this logic can be used in archiving some  document for example if you have a document which   are created or last modified let's say six  months ago or a year ago and you want to see   that if that is the case so you can archive those  documents so I'm done with the workflow now i will   click on save so the workflow is saved now i can  quickly test this workflow by clicking on the test   and i will say i will perform this action and  click on test and i will click on continue to   give my workflow permission to sharepoint and  then i will click on run flow and click done   now you can see the floor ran successfully if  i navigate back to my email i have received two   emails first email is saying the expense title is  accommodation expense and the day since created   is zero which means that i have just created few  minutes ago that's why the day difference is zero   but if i look into the other email this is the  travel expense and the days since created is 24   which is this travel expense it was created on  17th of august and today is 11th of September   so it's been 24 days since it's created just  to wrap up this video today you have learned   how you can easily calculate the difference  between the two dates in power automate using the   help of expressions consider subscribing if you're  new to the channel by pressing the red subscribe   button below this video give this video a thumbs  up if you found it useful if you have any queries   related to power automate or anything related  to office 365 in general post in the comment   section below i do review my comments on the  daily basis i will be happy to answer thank you

Channel: KeaPoint Tech Tips

Views: 30,431

Keywords: Calculate the Date Difference using Power Automate, how to calculate date difference, how to calculate days difference power automate, power automate regular expression, power automate working with dates, microsoft power automate, ms flow, date calculation in power automate, power automate date ticks, power automate list date column example, power auotmate date, power automate date functions, power automate date and time functions, keapoint tech tips, keapoint

Id: GbqvyjwiX6c

Length: 6min 54sec (414 seconds)

Published: Mon Sep 21 2020