Get the Number of Days Between Two Dates | Power Automate

hello power people and thank you for joining me Joe Unwin also known as flojo on another Power ultimate video today we're going to be looking at date differences and how we get two dates and find the differences between them the date and the time now there are two very different ways of doing this there is a Norway and an old way the old way is called ticks it's much more complicated to understand but it provides a more simpler result so it really depends on what you're trying to achieve with the date differences to which one you'll use but I'm going to actually show you both of them and you can then decide which one you want to use because sometimes you want to know the time and sometimes you don't so that's actually get started then what I've got is I've got a manual trigger flow and I've just got a compose with a start date and time the date is in the 2023 to Dash 12-01 and then the time is all zeros format now obviously this is the first of December 2023 at midnight then the end date I've got 2023-12-08 so I'm seven days forward but I'm also doing it at 10 am so the date difference is seven hours seven days and 10 hours so we know this just by looking at it right but we need power automate to calculate this because we may have loads of dates coming from dataverse from Excel from wherever and we need to automatically calculate this so how do we do it well the first way I'm going to show you is called the ticks method now a tick is a hundred nanosecond tick it's basically 100 nanoseconds each time that we can calculate to be a total number of nanoseconds in a day now if I've already lost you don't worry I'm going to go for it further but it's quite complicated because what we need to do and I'll highlight this on here it's quite small I'll put all of this in the description so you can actually read it further but essentially we need to divide the um ticks between each one which we subtract by a day sounds really complicated so let's go into the first function what we're doing is we're doing subtraction and we're doing ticks so what we're doing is we're doing a tick of the date and time and then we're doing another tick of the date and time and then we're subtracting both of them but we're starting at the end date and then we are passing in the start date so what we do is we subtract the end date from the start date in tick format so we convert them to a tick so 100 nanoseconds per uh per one calculates all the way up and each of them then we subtract both of them so we've got these two large numbers that we're subtracting and then what we do is we divide that very large number by this massive eight six four zero zero zero zero zero zero zero uh number now that 864 number is actually a day that is a day in nanoseconds so what we do is we calculate the ticks of each of the dates we subtract them we have a massive number then we divide it by the tick total of a day so we're simply dividing the differences by a day and then we end up with the total days left between them very complicated but this is a very good way of actually calculating days between dates because it does it simple you don't need to do anything to it but you don't have the time difference so let's just run a test and actually see the difference then a few moments later and let's see what we actually get back okay so we have a start date and time we have our end date and time we know it's 7 days and 10 hours so what do we get back with the tick method we get seven so we know there are seven days between them but what if we wanted to also know the hours between them as well so we want the days the hours the minutes the seconds any time that we want we don't want to use the tick method we want to use a different method which is a newer method so bear in mind that this returns a nice seven a nice easy number you can then just use this output and calculate yep it's seven days later or seven days earlier whichever way you're trying to work it out you can then um go and continue with your flow very easy nice you just get a single digit number and you can continue but if you use the newer method you get the time as well but you need to do some additional stuff to it to actually make sense of the data returned so let's go back into this so obviously we've got our start date and end time we're using exactly the same things but this time we're going into the date difference function so what is the difference here then well the date difference is a function that you need to pass the date and time so you need to use the pass date time function you need to pass in the date and time if it's not in a standard format so you would use past date time you would pass in the start time for example then you would do comma and then you need to do the location so in this instance I'm doing pass a start date and then I'm doing comma then I'm doing E N Dash c a because I'm in English Canada and you would do the same for the end date you would then do pass date time the output of end date and then comma enca and don't forget the single quotation marks around those now this looks very much more simpler right like you haven't got all these ticks things you've got to remember you haven't got this big huge 864 number that you have to like calculate for the days that you're doing Division and subtraction and all that but as you'll see there are negatives to the way of doing this and I'll show you that momentarily but simply put all you do is date difference you do your past date you do the start date first this time comma the location and then do another comma after you've closed the parentheses and do past date time again end date and the location so what I'm going to do now is I'm going to actually run this and see what we get back and then I'll show you how we can manipulate that data and handle it so I'm just going to run this test again run the flow and now what we've got back is we've got the start date and end date again remember we got seven back initially for the tick version but now on the date difference what do we get back we get 7.10 and then colon zero zero colon zero zero so what this is actually providing us is it's seven days 10 hours zero minutes zero seconds so we get all of that information back but you can't just go and use this because it doesn't make sense to use this anywhere right you're not just you've not got the date separate you've not got the time separate so you actually need to separate this and what you can actually do is you can actually just use the split function so if you do split well I'll just highlight over that so you can see it on the screen then I'll go through it you can just pass the output of the compose action the date difference function and then do comma single quotation marks and then the full stop because if you remember it was 7.10 so if we split it at that point that full stop we end up with seven for the days and then the time underneath it so if we run this actually what I'll do is I'll just show you on the expression box here just so you can see more clear right you just have outputs and then you have the comma and then the single quotation marks in the full stop right so then it's just splitting at that particular full stop and what it does is it removes the full stop as well so you just have your seven and then your time so let's just run this again and actually see it so again previously the ticks was seven nice and easy to use the date difference function was 7.10 Etc and now we're using the split we now have that into a seven and then we have the time in a different section so we've now created an array we've got seven for the days and then the actual time underneath it so now we have an easy way to break down that information we have how many days and then how much time so this is another way you can do it so if you're um if the information about the time is Key obviously use date difference it will provide you with the information that you're required because you're going to need the hours the minutes the seconds difference as well but my recommendation is if you're just looking at using the days then I would use the tick method but if you're looking for days and time then I would use the date difference method obviously then you can then pull this information out using first to just get the um uh the the days or you can just use the square brackets and just do one because this is a base of zero to get the time uh no that's obviously Json and if you want to find out any more information about that and how you retrieve that obviously just Google it or watch one of my other videos um that I've done previously I've done a video on how you can uh avoid using apply to each that goes through how you can use the square brackets to retrieve information from um arrays without having to apply to each for you so if you're looking at doing that then definitely check out that video but that is how you can tell the difference between two dates for both the days and the times on Parallel to me now we're trying to get to 10 000 subscribers by the end of the year so make sure you hit that subscribe button and that like button to help out the channel and the video really appreciate you watching and see you next time

Channel: FlowJoe

Views: 3,161

Keywords: Power Automate, Date Calculation, Days Between Dates, Workflow Automation, Microsoft Power Platform, Date Functions, Tutorial, Learning, Efficient Methods, Process Improvement, Automation Techniques, Power Automate Tips, Time-saving Tricks, Old vs New Methods, Microsoft Flow, Flow, Date Difference, Ticks, Tick, Days Dates, Number of Days, power automate number of days between two dates

Id: oJd7DDzZ5T4

Length: 11min 50sec (710 seconds)

Published: Mon Aug 14 2023