hello folks welcome back in today's video i will show you
how to calculate difference between two dates in power automate 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 automate 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 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 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 date diff which is difference 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 get 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 SharePoint 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 flow 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