cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

How to calculate difference between DateTime values on different rows?

I'm trying to create a flow that calculates the time difference between timestamps. Each timestamp has only a start time so the time difference needs to be calculated between DateTime values on different rows of the same user.

 

I'm using Dataverse, here is sample data:

Screenshot 2023-04-04 at 11.27.22.png

 

The desired output for User 1 is 60 (in minutes) and for User 2 is 120 (minutes).

 

This is where I'm stuck. I'm able to get the ticks for the timestamp but I have no idea how to compare it to the previous timestamp of the same user so I could calculate the difference.

 

Screenshot 2023-04-04 at 11.34.22.png

 

17 REPLIES 17
abm
Most Valuable Professional
Most Valuable Professional

Hi @ramzez 

 

Do you have only two records for each user? 

 

Thanks



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog
Power Automate Video Tutorials

No, that is just the sample data that I'm using to try to get the flow to work. The actual table will have multiple users and each will have multiple timestamps in varying order.

abm
Most Valuable Professional
Most Valuable Professional

Hi @ramzez 

 

So if you have three times for the same user how you want to workout?

 

Thanks



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog
Power Automate Video Tutorials

Sorry I didn't provide enough information. 

 

Here is the sample date with some more info to make it more clear.

Screenshot 2023-04-04 at 15.03.20.png

 

Each row represents a timestamp that the user will make when starting to work or when going on a break. I need to calculate the duration that the users have worked (start time = "work", end time = "break") and the duration on break ("start time = "break", end time = "work"). I need to calculate time difference for each consecutive pair of timestamps for each user because the timestamp only has a "start time" so the next timestamp for the user will be the "end time" for the duration. I will then save the calculated durations to another table for further processing.

 

 

abm
Most Valuable Professional
Most Valuable Professional

Hi @ramzez 

 

Thanks for the explanation. Believe you are looking the total number of working hours here (per user).



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog
Power Automate Video Tutorials

Yes, that is what the end goal is but for now I’m looking for a solution how to calculate the duration of the work and break sessions. I can then save them to another table for further use. I can then jus sum up the values rom that table.

 

Another way could be to save the timestamps to another table so that the start time stamp and end time stamp would be on the same row for ach work and break session. Then a formula column could calculate the time difference for each row.

I think it would be very difficult to be consistent with all of the data on individual rows. Unless you know for sure that some things can't happen like:

Employee never works across dates like 10pm-2am

Employees first time for a date is always the work start time and the last row for an employee is the end time

Employee never ends on a break

The flow only runs when everyone is ended, like it couldn't run if someone just has a start time

I could probably think of more ways it would be a problem

 

If you can get the times in a single row, it would be a huge help and make things much easier. Getting the start work day time, end work day time, and any start break time and end break time would just require time difference. 

 

Where is this time coming from? Are people filling out some form or a schedule tool?

abm
Most Valuable Professional
Most Valuable Professional

Hi @ramzez 

 

I got a solution for this. Will update soon.

 

Thanks



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog
Power Automate Video Tutorials

Employees work across dates, employees first timestamp for a date is not necessarily work start time, the last for a date is not necessarily work end time and it can end on a break.

 

The timestamps come from a system where employees indicate when they start working, what kind of work they start, when they start a break and when they leave work. In the sample data there is just a single "WORK" timestamp, but in reality there are about 10 different timestamps. If I can figure out how to make it work for one type of stamp, I could replicate the logic across all timestamp types.

 

Overall there are about 300 employees out of which some of them might make over 1000 timestamps a month so I'm beginning to think that maybe Power Automate isn't the right tool for the job. Maybe I'll also look into Dataflows.

abm
Most Valuable Professional
Most Valuable Professional

Hi @ramzez 

 

Believe the first entry starts with work and next is a break which might be the end of the work for the day or a break. Please confirm.

 

Thanks



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog
Power Automate Video Tutorials

I would have the flow run once per day and fetch timestamps from xx days so the first entry could be anything and the next one could also be anything. There could multiple simultaneous timestamps created so I'm not sure if a trigger like "When a row is added" can be used. Also a timestamp can be modified or deleted if an employee has made a mistake. There would be a separate timestamp "OFFWORK" when leaving work.

abm
Most Valuable Professional
Most Valuable Professional

Hi @ramzez 

 

I am thinking when ever a record (type break) is created you need to retrieve the previous record (type work) then calculate the time difference. What's your work timings? Is it 24 hour clocking with multiple shifts?



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog
Power Automate Video Tutorials

Yes, it is 24 hour clocking with multiple shifts and there are multiple locations where these timestamps are created simultaneously.

abm
Most Valuable Professional
Most Valuable Professional

Hi @ramzez 

It depends on how you design it.  So here I am working out the previous work value and if the current type is break then working out the difference in minutes. For this design you need two flows. One for when a record is created. Another flow is set the from and date against the Time and get the Total hours worked for each users. All we need to do is add the Hoursworked from the column for each user. What do you think?

 

image.png



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog
Power Automate Video Tutorials

I think you are doing too much work. If the time stamps from the system have multiple different types, then they should be used accordingly. It would make it easier and prevent errors.

OP is making the solution harder by not providing values that could help differentiate and add times together.

 

I don't know what time clock would require you to Clock In, Clock Out on Break, Clock Out of Work. The breaks would have to be quantified some other way to know how long the break was. Like is every break and hour??

 

Also, OP didn't say the type would be Work Start or Work End. If you have those values then you can easily get the first Start Work time and find the next End Work time.

 

I'm having a hard time understanding how a Time Clock System output is requiring data backflips and comparisons to make a simple time worked summary.

Thank you for your help and effort but I've realised that Power Automate doesn't seem to be right tool for this. I was able to do the necessary calculations and data manipulations with a Dataverse dataflow and Power Query.

abm
Most Valuable Professional
Most Valuable Professional

Hi @ramzez 

 

Check your email please?

 

Thanks



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog
Power Automate Video Tutorials

Helpful resources

Announcements

Community will be READ ONLY July 16th, 5p PDT -July 22nd

Dear Community Members,   We'd like to let you know of an upcoming change to the community platform: starting July 16th, the platform will transition to a READ ONLY mode until July 22nd.   During this period, members will not be able to Kudo, Comment, or Reply to any posts.   On July 22nd, please be on the lookout for a message sent to the email address registered on your community profile. This email is crucial as it will contain your unique code and link to register for the new platform encompassing all of the communities.   What to Expect in the New Community: A more unified experience where all products, including Power Apps, Power Automate, Copilot Studio, and Power Pages, will be accessible from one community.Community Blogs that you can syndicate and link to for automatic updates. We appreciate your understanding and cooperation during this transition. Stay tuned for the exciting new features and a seamless community experience ahead!

Summer of Solutions | Week 4 Results | Winners will be posted on July 24th

We are excited to announce the Summer of Solutions Challenge!    This challenge is kicking off on Monday, June 17th and will run for (4) weeks.  The challenge is open to all Power Platform (Power Apps, Power Automate, Copilot Studio & Power Pages) community members. We invite you to participate in a quest to provide solutions to as many questions as you can. Answers can be provided in all the communities.    Entry Period: This Challenge will consist of four weekly Entry Periods as follows (each an “Entry Period”)   - 12:00 a.m. PT on June 17, 2024 – 11:59 p.m. PT on June 23, 2024 - 12:00 a.m. PT on June 24, 2024 – 11:59 p.m. PT on June 30, 2024 - 12:00 a.m. PT on July 1, 2024 – 11:59 p.m. PT on July 7, 2024 - 12:00 a.m. PT on July 8, 2024 – 11:59 p.m. PT on July 14, 2024   Entries will be eligible for the Entry Period in which they are received and will not carryover to subsequent weekly entry periods.  You must enter into each weekly Entry Period separately.   How to Enter: We invite you to participate in a quest to provide "Accepted Solutions" to as many questions as you can. Answers can be provided in all the communities. Users must provide a solution which can be an “Accepted Solution” in the Forums in all of the communities and there are no limits to the number of “Accepted Solutions” that a member can provide for entries in this challenge, but each entry must be substantially unique and different.    Winner Selection and Prizes: At the end of each week, we will list the top ten (10) Community users which will consist of: 5 Community Members & 5 Super Users and they will advance to the final drawing. We will post each week in the News & Announcements the top 10 Solution providers.  At the end of the challenge, we will add all of the top 10 weekly names and enter them into a random drawing.  Then we will randomly select ten (10) winners (5 Community Members & 5 Super Users) from among all eligible entrants received across all weekly Entry Periods to receive the prize listed below. If a winner declines, we will draw again at random for the next winner.  A user will only be able to win once overall. If they are drawn multiple times, another user will be drawn at random.  Individuals will be contacted before the announcement with the opportunity to claim or deny the prize.  Once all of the winners have been notified, we will post in the News & Announcements of each community with the list of winners.   Each winner will receive one (1) Pass to the Power Platform Conference in Las Vegas, Sep. 18-20, 2024 ($1800 value). NOTE: Prize is for conference attendance only and any other costs such as airfare, lodging, transportation, and food are the sole responsibility of the winner. Tickets are not transferable to any other party or to next year’s event.   ** PLEASE SEE THE ATTACHED RULES for this CHALLENGE**   Week 1 Results: Congratulations to the Week 1 qualifiers, you are being entered in the random drawing that will take place at the end of the challenge.   Community MembersNumber SolutionsSuper UsersNumber Solutions Deenuji 9 @NathanAlvares24  17 @Anil_g  7 @ManishSolanki  13 @eetuRobo  5 @David_MA  10 @VishnuReddy1997  5 @SpongYe  9JhonatanOB19932 (tie) @Nived_Nambiar  8 @maltie  2 (tie)   @PA-Noob  2 (tie)   @LukeMcG  2 (tie)   @tgut03  2 (tie)       Week 2 Results: Congratulations to the Week 2 qualifiers, you are being entered in the random drawing that will take place at the end of the challenge. Week 2: Community MembersSolutionsSuper UsersSolutionsPower Automate  @Deenuji  12@ManishSolanki 19 @Anil_g  10 @NathanAlvares24  17 @VishnuReddy1997  6 @Expiscornovus  10 @Tjan  5 @Nived_Nambiar  10 @eetuRobo  3 @SudeepGhatakNZ 8     Week 3 Results: Congratulations to the Week 3 qualifiers, you are being entered in the random drawing that will take place at the end of the challenge. Week 3:Community MembersSolutionsSuper UsersSolutionsPower Automate Deenuji32ManishSolanki55VishnuReddy199724NathanAlvares2444Anil_g22SudeepGhatakNZ40eetuRobo18Nived_Nambiar28Tjan8David_MA22   Week 4 Results: Congratulations to the Week 4 qualifiers, you are being entered in the random drawing that will take place at the end of the challenge. Week 4:Community MembersSolutionsSuper UsersSolutionsPower Automate Deenuji11FLMike31Sayan11ManishSolanki16VishnuReddy199710creativeopinion14Akshansh-Sharma3SudeepGhatakNZ7claudiovc2CFernandes5 misc2Nived_Nambiar5 Usernametwice232rzaneti5 eetuRobo2   Anil_g2   SharonS2  

Check Out | 2024 Release Wave 2 Plans for Microsoft Dynamics 365 and Microsoft Power Platform

On July 16, 2024, we published the 2024 release wave 2 plans for Microsoft Dynamics 365 and Microsoft Power Platform. These plans are a compilation of the new capabilities planned to be released between October 2024 to March 2025. This release introduces a wealth of new features designed to enhance customer understanding and improve overall user experience, showcasing our dedication to driving digital transformation for our customers and partners.    The upcoming wave is centered around utilizing advanced AI and Microsoft Copilot technologies to enhance user productivity and streamline operations across diverse business applications. These enhancements include intelligent automation, AI-powered insights, and immersive user experiences that are designed to break down barriers between data, insights, and individuals. Watch a summary of the release highlights.    Discover the latest features that empower organizations to operate more efficiently and adaptively. From AI-driven sales insights and customer service enhancements to predictive analytics in supply chain management and autonomous financial processes, the new capabilities enable businesses to proactively address challenges and capitalize on opportunities.    

Updates to Transitions in the Power Platform Communities

We're embarking on a journey to enhance your experience by transitioning to a new community platform. Our team has been diligently working to create a fresh community site, leveraging the very Dynamics 365 and Power Platform tools our community advocates for.  We started this journey with transitioning Copilot Studio forums and blogs in June. The move marks the beginning of a new chapter, and we're eager for you to be a part of it. The rest of the Power Platform product sites will be moving over this summer.   Stay tuned for more updates as we get closer to the launch. We can't wait to welcome you to our new community space, designed with you in mind. Let's connect, learn, and grow together.   Here's to new beginnings and endless possibilities!   If you have any questions, observations or concerns throughout this process please go to https://aka.ms/PPCommSupport.   To stay up to date on the latest details of this migration and other important Community updates subscribe to our News and Announcements forums: Copilot Studio, Power Apps, Power Automate, Power Pages

Users online (550)