• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Omit Overlapping Hours when Adding Total Hours

jbureaux

New Member
Hello!
I'm running into an issue when adding up my clients total hours at the end of the month.
The excel sheet is currently exported from Monday, with time tracked within the Monday platform. Attached is the CSV file that gets exported, which is currently organized by task.

The issue is, the desired total hours should not be all the hours added up by individual employees, but should be our company's total hours spent - so no overlap if two people are working at the same time.

For the moment, I went in manually and reorganized the sheet by date then cross referenced the time and deleted any overlapping hours, but there has to be a more efficient way to do this.

For example, here is a snippet of the data:
2021-09-06
2021-09-06
11:26 AM​
3:36 PM​
4h 9m 25s​
2021-09-06
2021-09-06
12:58 PM​
1:46 PM​
47m 42s​

Since these two were happening at the same time, the desired total hours worked in the above case should be 4hrs 9m 25s, not 4hrs 57m 7s (4hrs 9m 25s + 0hr +47m+42s)

Please let me know if there is any solution to this or any suggestion that could make the desired outcome easier, I'm not sure if this is something I can achieve in Excel or through my time tracking management software.

Thanks!!
 

Attachments

  • time_tracking_log_example.xlsx
    14.3 KB · Views: 5
Last edited:
jbureaux
Attached is the CSV file that gets exported ...
Do You refer to that en Excel-file?
About Your snippet ... isn't there whole four seconds gap (between 16 and 20)?
 
jbureaux
Attached is the CSV file that gets exported ...
Do You refer to that en Excel-file?
About Your snippet ... isn't there whole four seconds gap (between 16 and 20)?

Thanks for pointing that out, yes I pulled from the master excel doc. I just adjusted my original post with a better example

2021-09-06
2021-09-06
11:26 AM​
3:36 PM​
4h 9m 25s​
2021-09-06
2021-09-06
12:58 PM​
1:46 PM​
47m 42s​
 
Is it then not simply min of start hour and max of end hour per day? Or what do I miss?

We have a number of different clients - some days we won't be working for some clients and some days we are, sometimes there are multiple employees working across different clients so the hours are never really have a standard end and start time
 
@GraH - Guido
What is missing is that there might be times during the day where no staff member is present.
The solution requires the arrival/departures to be recorded in time order. One then accumulates the staff numbers (adding 1 for an arrival and subtracting for a departure) and differences for each duration. Ignore the time periods that are not staffed and add the rest.

For me the solution uses
Code:
= SCAN(0, change, LAMBDA(acc, delta, acc+delta)) - change
but it should be simpler for you with single cell relative referencing. No point in my doing it, you will be far better at it.
 
@GraH - Guido
What is missing is that there might be times during the day where no staff member is present.
The solution requires the arrival/departures to be recorded in time order. One then accumulates the staff numbers (adding 1 for an arrival and subtracting for a departure) and differences for each duration. Ignore the time periods that are not staffed and add the rest.

For me the solution uses
Code:
= SCAN(0, change, LAMBDA(acc, delta, acc+delta)) - change
but it should be simpler for you with single cell relative referencing. No point in my doing it, you will be far better at it.

I'm not sure how efficient this will be given that most of our employees are remote, some international. While our HQ offices are typically 10am EST-7pm EST we usually have employees remote working around the clock in different time zones so it would be hard for us to work in hours for a typical work day and subtract down time.
 
Last edited:
We have a number of different clients - some days we won't be working for some clients and some days we are, sometimes there are multiple employees working across different clients so the hours are never really have a standard end and start time
Thanks for clarifying. I was without excel so could not see the data.
 
In the attached, there's a sheet log (2) which is a copy of yours but I've made a few alterations to be able to make a Table called Table1 of cells A4:F250:
Moved Task 1 in cell A4 to A5, then created a header Task in A4, then created the table.

I added a Power Query query at cell I4.
The data in the blue Table1 doesn't need to be sorted by date/time, the query does that.
As you'll see the total hours (Duration) match exactly those in your column G, which is because there are no overlapping periods in any of the tasks, however if there were any, they'd be processed properly.
To test it out, change some of the data in the blue table, then go to the results table, right-click and choose Refresh.
Or get some completely new data in, change the vertical extent of the blue table to fit, make sure it's still called Table1 and that the headers remain the same, then refresh the results table.
 

Attachments

  • Chandoo46925time_tracking_log_example2.xlsx
    40.1 KB · Views: 5
Back
Top