Overlapping Duration

[aliefan]

Hi, can someone help me how to sum work hour/ work duration avoiding overlapping duration.

For example i work from 07.00 to 08.00 and at the same time i have meeting from 07.30 to 08.00. Valid working hour should be 1 hour, not 1 hour 30 minutes

[Marc_Dillon]

There are various methods, depending on your exact data and app setup. Kind of hard to give you a solution without knowing more about your setup.

One way would be lots of IF/IFS checks to find the duration and avoid the overlaps. For example:
IF( [start2]>=[end1] , [end1]-[start1] + [end2]-start2] , [end2]-[start1] )
Hope you can see this could get very messy very quick.

Another method would be to more simply record your daily singular start and end, and then any breaks. So the end calculation would be [end]-[start] - SUM( breaks ).

[aliefan]

nah the problem is, i have more than 1 thousand of row. its impossible to check 1 by 1 using IFS

[Marc_Dillon]

---

@Marc_Dillon wrote:

Kind of hard to give you a solution without knowing more about your setup.

---

[Sirfyaad]

@aliefan 

Calculate the duration of each work session. In this column, you can use the following formula or expression:

=IF([End Time] < [Start Time], 0, [End Time] - [Start Time])

"Total Work Hours" to sum the valid work durations. You can use a formula or expression like this:

=SUMIF([Duration], ">0")

[aliefan]

i think it only work in one row condition, i have a thousand of row of overlapping duration. any other ideas?

[bmpoweravn]

You ca try this. I am using this in my Attendance app.

(TIME([Time Out Log])-TIME(SELECT(Attendance In[Time in Log],[Attendance ID]=[_THISROW].[Attendance ID])))-LOOKUP([_THISROW].[Attendance ID],"Break In","Attendance ID","Total Time Break")