How to calculate difference between DateTime values on different rows?

[ramzez]

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:

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.

 

 

[abm]

Hi @ramzez 

Do you have only two records for each user? 

Thanks

[ramzez]

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]

Hi @ramzez 

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

Thanks

[ramzez]

Sorry I didn't provide enough information. 

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

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]

Hi @ramzez 

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

[ramzez]

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.

[wskinnermctc]

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]

Hi @ramzez 

I got a solution for this. Will update soon.

Thanks

[ramzez]

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]

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

[ramzez]

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]

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?

[ramzez]

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

[abm]

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?

[wskinnermctc]

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.

[ramzez]

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]

Hi @ramzez 

Check your email please?

Thanks