+ Reply to Thread
Results 1 to 58 of 58

calculate total time a worker has in team on a site on a certain date

  1. #1
    Registered User
    Join Date
    11-14-2018
    Location
    Belgium
    MS-Off Ver
    Microsoft office Professional Plus 2019
    Posts
    18

    calculate total time a worker has in team on a site on a certain date

    Good day dear Expert,

    For a work project I need to sum the total time an employee spends together with a colleague on a specific site in a month.
    Some sites are worked alone while others are together with someone else. They do not always start and end at the same time on a specific site. Could be that one has been working already for an hour when the colleague arrives and leaves again before the other one ends his shift ect.
    The time stamps are detailed to within a quarter of an hour.

    I have a full list of all the employees and their Ins&Outs per day on the different sites, problem is that this is for a huge company and a months worth of data gives me 70K+ lines.
    Hence why posting this in the VBA section, a macro seems to be the only way to be able to calculate a solution without exploding my pc.

    I have worked the file and found a semi solution but, as the file is huge, and calculations include multiple countifs and sumifs, it takes ages to calculate.

    My solution at this time was to split up the day in timestamps of half an hour (=48 column) in which I check:
    - Has the worker worked in a specific timestamp (ex. 15:30 - 16:00) then 1 otherwise 0
    - pivot table per site per day for each timestamp to have to total number of people working on a site in each timestamp
    - Check per worker if he has worked in a timestamp and if there were +1 people active during that timestamp that day on that project to sum half an hour to his 'total time worked in group'


    Some extra points of detail:
    - a worker can work on multiple sites within a day
    - he can also have multiple shift on a day on the same site (ex. 10:00-12:00 and 16:00-18:00) => these will show as 2 lines in the raw data

    What would you need as sample - extra information?
    I've added a sample sheet of data with which I have to work and a column with the desired outcome, to clarify what I'm looking for in cas that was not clear.... This does not include the calculations I have come up with myself as noted above, don't think they would help a lot but sure can if required, unfortunately my VBA knowledge is close to 0 at the moment so cannot share a lot on that front...

    Thank you for the help, more than welcome to answer your questions to be more detailed!
    Greetz, Robin
    Attached Files Attached Files
    Last edited by sean connerie; 12-15-2022 at 05:15 AM. Reason: solved by Bsalv

  2. #2
    Forum Contributor
    Join Date
    11-23-2022
    Location
    Amsterdam
    MS-Off Ver
    2021
    Posts
    193

    Re: calculate total time a worker has in team on a site on a certain date

    First I had to reformat the time columns as it included a date (Jan 1st 1900).
    This code will put the shared times in column H:

    Please Login or Register  to view this content.
    I noticed that in some case my results deviate but perhaps I have missed something in your reasoning.

  3. #3
    Valued Forum Contributor
    Join Date
    08-08-2022
    Location
    Buenos Aires
    MS-Off Ver
    Excel 2019
    Posts
    1,777

    Re: calculate total time a worker has in team on a site on a certain date

    Hello.

    Please clarify the following for me: Are you interested in considering workers who coincide in a time range or workers who, in addition to coinciding in a time range, are working at the same site? (They are two different criteria, it is understood).
    You are always very welcome if you add reputation by clicking the * (bottom left) of each message that has helped you.

  4. #4
    Valued Forum Contributor
    Join Date
    08-08-2022
    Location
    Buenos Aires
    MS-Off Ver
    Excel 2019
    Posts
    1,777

    Re: calculate total time a worker has in team on a site on a certain date

    One more question: Can it be the case that there is a time coincidence between 3 or more workers?...

  5. #5
    Registered User
    Join Date
    11-14-2018
    Location
    Belgium
    MS-Off Ver
    Microsoft office Professional Plus 2019
    Posts
    18

    Re: calculate total time a worker has in team on a site on a certain date

    Hi Kaasplankje,

    That works wonders! I did the 'desired outcome' calculation in column G by hand without rechecking and your code rectified the multiple errors I made lol so hats off!

    I do wonder, and that is a point of "Beyond Excel" here below as well, if your code stops at a certain point with checking if there are matching lines in the dataset.
    - As this was a sample form it did include the full dataset where there can me as much as like 80 different people working on a same site on the same day.
    (if I understand your code correctly it only checks up to 4 different matches?)
    - Again going off my limited understanding of your code, it works only if the dataset is sorted as is in the sample sheet, so that all people at the same time on site are listed below each other?
    This can be done ofcourse so not a necessity to find a solution for but asking more for confirmation that I would need to apply the sorting before running your code

    thank you!

  6. #6
    Registered User
    Join Date
    11-14-2018
    Location
    Belgium
    MS-Off Ver
    Microsoft office Professional Plus 2019
    Posts
    18

    Re: calculate total time a worker has in team on a site on a certain date

    Hi Beyond Excel,

    The second option you give:
    workers who coincide in a time range (so date and time together), AND that are working at the same site

    It can certainly be the care that there are more coincides than 3. The full dataset gives matches to up to 80 different people working on the same date on the same site. (should have specified this is the initial thread...)

    thank you!

  7. #7
    Forum Contributor
    Join Date
    11-23-2022
    Location
    Amsterdam
    MS-Off Ver
    2021
    Posts
    193

    Re: calculate total time a worker has in team on a site on a certain date

    This should work for any number of people working on the same site (and the same day):

    (code deleted by accident)
    Last edited by kaasplankje; 12-02-2022 at 10:40 AM.

  8. #8
    Registered User
    Join Date
    11-14-2018
    Location
    Belgium
    MS-Off Ver
    Microsoft office Professional Plus 2019
    Posts
    18

    Re: calculate total time a worker has in team on a site on a certain date

    Hi!
    This seems to be able to run for any number of lines and combinations!

    I do have another complexity that should be added if you're still up for it At the moment the code only works for day shifts correct?
    due to the AND condition that : "entry.Offset(0, 3) < matching_entry.Offset(0, 4)"

    So if there is a night shift (=end time < begin time) then it won't start calculating the shared_Time?

    Would that be possible to add in your coding as well? I've added a new version of the sample file with some night shifts included in case that could help out

    thank you!
    Robin
    Attached Files Attached Files

  9. #9
    Forum Contributor
    Join Date
    11-23-2022
    Location
    Amsterdam
    MS-Off Ver
    2021
    Posts
    193

    Re: calculate total time a worker has in team on a site on a certain date

    It is possible, but requires some workarounds due to the messy way Excel treats times (and dates). What appears to be a proper time is actually treated in excel as a number, only formatted such that it appears as time. Moreover, a time such as 25:00:00 is automatically converted to 01:00:00. I really wish Microsoft could do something about this but fear that they won't read this post ;-)

    Taking the above into account, this is what I could make of it:

    Please Login or Register  to view this content.
    Last edited by kaasplankje; 12-02-2022 at 09:46 AM.

  10. #10
    Valued Forum Contributor
    Join Date
    08-08-2022
    Location
    Buenos Aires
    MS-Off Ver
    Excel 2019
    Posts
    1,777

    Re: calculate total time a worker has in team on a site on a certain date

    Hi @sean connerie.
    I propose to deeply analyze the following 3 people who coincided in date and place:


    - Jean y Robert coincided 7 hours.
    - Jean y Tweeny coincided 3 hours.
    - Robert y Tweeny coincided 3:30 hours.

    So: What is the result you hope to obtain?... Better upload a workbook with such a result developed by hand to the Forum, please.

  11. #11
    Valued Forum Contributor
    Join Date
    08-08-2022
    Location
    Buenos Aires
    MS-Off Ver
    Excel 2019
    Posts
    1,777

    Re: calculate total time a worker has in team on a site on a certain date

    I show you a solution along the lines of what was discussed in post #10:

    Please Login or Register  to view this content.
    P.S.
    See post #16.
    Last edited by beyond Excel; 12-03-2022 at 11:29 AM.

  12. #12
    Registered User
    Join Date
    11-14-2018
    Location
    Belgium
    MS-Off Ver
    Microsoft office Professional Plus 2019
    Posts
    18

    Re: calculate total time a worker has in team on a site on a certain date

    Hi again,
    That seems to do the trick on the samplesheet I shared! il calculated correctly the shared time, during the night as well :D :D

    However, I tried to apply the same thing to my full database (over 70K rows) and it Excel has been bugged calculating for the last half hour almost without any movement.

    - did you add a line that it only updates the full list at the end? I read that last line "Application.ScreenUpdating = True" as such
    => Would there be a way to speed up the calculation, or do I just leave it running and hope that it does the trick? :p

    thanks so much already! Let me know when I ask for too much lol

  13. #13
    Valued Forum Contributor
    Join Date
    08-08-2022
    Location
    Buenos Aires
    MS-Off Ver
    Excel 2019
    Posts
    1,777

    Re: calculate total time a worker has in team on a site on a certain date

    - Errors can not be any.
    - Processing time should not be more than 1-2 minutes.

    Probably "something" in the source data has not been presented like what we saw in your previous workbooks: Have to see that!

  14. #14
    Valued Forum Contributor
    Join Date
    08-08-2022
    Location
    Buenos Aires
    MS-Off Ver
    Excel 2019
    Posts
    1,777

    Re: calculate total time a worker has in team on a site on a certain date

    Now I am in doubt: Does your comment in post #12 refer to my proposal in post #11 or to the proposal of my partner?

  15. #15
    Forum Contributor
    Join Date
    11-23-2022
    Location
    Amsterdam
    MS-Off Ver
    2021
    Posts
    193

    Re: calculate total time a worker has in team on a site on a certain date

    Think his reply was to me.

    @sean: Can imagine it's slow. On my mchine it processes 130 entries for a 50k dataset. Perhaps you can insert a line with Application.StatusBar = entry.Row to see where the routine is in the list?

  16. #16
    Valued Forum Contributor
    Join Date
    08-08-2022
    Location
    Buenos Aires
    MS-Off Ver
    Excel 2019
    Posts
    1,777

    Re: calculate total time a worker has in team on a site on a certain date

    Hello.
    I am attaching a new version that is more efficient than the old one: three/four seconds for 11 thousand rows of data.
    Attached Files Attached Files

  17. #17
    Forum Contributor
    Join Date
    11-23-2022
    Location
    Amsterdam
    MS-Off Ver
    2021
    Posts
    193

    Re: calculate total time a worker has in team on a site on a certain date

    That's very impressive. I am going to analyze your code to the bone!

  18. #18
    Forum Contributor
    Join Date
    11-23-2022
    Location
    Amsterdam
    MS-Off Ver
    2021
    Posts
    193

    Re: calculate total time a worker has in team on a site on a certain date

    I have come to realize that using For Each loops was not the best thing. Provided the data are date-sorted, the following code should be competitive:

    Please Login or Register  to view this content.
    Eager to know how fast it is on the real dataset.
    Last edited by kaasplankje; 12-04-2022 at 03:51 AM.

  19. #19
    Forum Expert
    Join Date
    01-25-2011
    Location
    Belgium, Alveringem
    MS-Off Ver
    Excel 2003, 2007, 365
    Posts
    1,418

    Re: calculate total time a worker has in team on a site on a certain date

    @ kaasplankje, you have to avoid interaction with your worksheet during the macro ...
    Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

  20. #20
    Forum Contributor
    Join Date
    11-23-2022
    Location
    Amsterdam
    MS-Off Ver
    2021
    Posts
    193

    Re: calculate total time a worker has in team on a site on a certain date

    For it to become superfast, you mean?

    I suppose putting the data in an array is indeed better, but as I am loading the dataset in the memory I thought this could be quite fas as well. No?

  21. #21
    Forum Expert
    Join Date
    01-25-2011
    Location
    Belgium, Alveringem
    MS-Off Ver
    Excel 2003, 2007, 365
    Posts
    1,418

    Re: calculate total time a worker has in team on a site on a certain date

    everytime you see dataset in your code (= reading or writing), it causes a delay.
    @Beyond Excel has 1 read , 1 delete, 1 write and 1 formatchange.
    With 11k rows, your code 'll be factor 10-100 slower.
    Last edited by bsalv; 12-04-2022 at 10:13 AM.

  22. #22
    Forum Contributor
    Join Date
    11-23-2022
    Location
    Amsterdam
    MS-Off Ver
    2021
    Posts
    193

    Re: calculate total time a worker has in team on a site on a certain date

    Thank you for clarifying, bsalv. I'm intrigued.

  23. #23
    Forum Contributor
    Join Date
    11-23-2022
    Location
    Amsterdam
    MS-Off Ver
    2021
    Posts
    193

    Re: calculate total time a worker has in team on a site on a certain date

    Btw I timed it, and for the 77 provided entries, my code takes 33 ms to finish and that of beyond excel only 17.

  24. #24
    Forum Expert
    Join Date
    01-25-2011
    Location
    Belgium, Alveringem
    MS-Off Ver
    Excel 2003, 2007, 365
    Posts
    1,418

    Re: calculate total time a worker has in team on a site on a certain date

    that was with those 20(?) rows as example ? Of course, now there is a factor 2, but as the number of interactions 'll increment ...
    I'll make an example after supper.

    Otherwise the error in time-measurement is size order several tenths of a second, so to be sure about the difference, make a loop, measure the time for 20 times your macro and do the same for @Beyond Excel.
    Last edited by bsalv; 12-04-2022 at 02:18 PM.

  25. #25
    Forum Contributor
    Join Date
    11-23-2022
    Location
    Amsterdam
    MS-Off Ver
    2021
    Posts
    193

    Re: calculate total time a worker has in team on a site on a certain date

    Actually with the 77 entries in Timesheets v2. But still a small number, I know.

    Looking forward to your next post. Eager to learn.

  26. #26
    Forum Contributor
    Join Date
    11-23-2022
    Location
    Amsterdam
    MS-Off Ver
    2021
    Posts
    193

    Re: calculate total time a worker has in team on a site on a certain date

    Quote Originally Posted by bsalv View Post
    that was with those 20(?) rows as example ? Of course, now there is a factor 2, but as the number of interactions 'll increment ...
    I'll make an example after supper.

    Otherwise the error in time-measurement is size order several tenths of a second, so to be sure about the difference, make a loop, measure the time for 20 times your macro and do the same for @Beyond Excel.
    In that case it's 0,25s for beyond excel and 4,8s for mine.

  27. #27
    Forum Expert
    Join Date
    01-25-2011
    Location
    Belgium, Alveringem
    MS-Off Ver
    Excel 2003, 2007, 365
    Posts
    1,418

    Re: calculate total time a worker has in team on a site on a certain date

    now 11.500 rows.
    Beyond= 0.55 sec and sum of common hours = 10.268 hours
    Kaasplankje = surprising good = 3.92 sec = only factor 7 and sum of common hours = 15.032 hours.

    Why do you have 5.000 extra hours ?
    Attached Files Attached Files

  28. #28
    Forum Contributor
    Join Date
    11-23-2022
    Location
    Amsterdam
    MS-Off Ver
    2021
    Posts
    193

    Re: calculate total time a worker has in team on a site on a certain date

    I think I have found it. If you look at, for example Tambo, Jambo and Rambo on 2.3.2020, we have the following results:

    ex1.jpg

    ex2.jpg

    From that you can see I mention Tambo only once, whilst bsalv is mentioning him (guess it's a him) twice. I think mentioning him once is more correct, as the question was how long each person worked together with another person. It obviously raises another question, which is: what if one person overlaps with one co-worker for a first time period and with another time period not overlapping with the first one. Not sure if my routine will capture that...

    I further discovered that Britney was not processed in my routine for March 5th in overlap with Tao (2 hours). Funnily, bsalv's routine mentions an overlap of 3 hours for both, which is also not correct.

    Ah, the fun we have!
    Attached Images Attached Images
    Last edited by kaasplankje; 12-04-2022 at 04:32 PM.

  29. #29
    Forum Expert
    Join Date
    01-25-2011
    Location
    Belgium, Alveringem
    MS-Off Ver
    Excel 2003, 2007, 365
    Posts
    1,418

    Re: calculate total time a worker has in team on a site on a certain date

    i didn't write a code, i applied @Beyond Excel and your code and compaired both

  30. #30
    Forum Contributor
    Join Date
    11-23-2022
    Location
    Amsterdam
    MS-Off Ver
    2021
    Posts
    193

    Re: calculate total time a worker has in team on a site on a certain date

    Yes, sorry. Already corrected.

  31. #31
    Registered User
    Join Date
    11-14-2018
    Location
    Belgium
    MS-Off Ver
    Microsoft office Professional Plus 2019
    Posts
    18

    Re: calculate total time a worker has in team on a site on a certain date

    Hi Alll,
    glad to see that the thread is catching up
    • Beyond Excel
      Thank you very much for your proposal! It surely is a new way to look at it and it makes it much easier to compare.
      The calculation of the shared time is, however, not my final destination in this excersise. Ultimately I'm looking for the % of time that workers have spend 'in team' compared to their total time worked (in a month)
      I did not want clubber up my thread with unecessary info, hence why I left it out at first :/

      Your new way of presenting is not really helping that ultimate goal for this excercise as I need to continue with calculations.
      My plan was to have the shared time in a column next to the data (like the way Kaasplankjes solution works) and the total time of that shift next to it and simply work with a pivot table to calculate the sum in the month

      Furthurmore, I think that the way your coding is looking for matching values, it misses out on possible matches. it's a point raised in the discussion between Kaasplankje and bsalv above:
      It obviously raises another question, which is: what if one person overlaps with one co-worker for a first time period and with another time period not overlapping with the first one.
      It does not matter with which person the work time overlaps. The only condition is that is has to be on the same Site ID and on the same date. Person A can thus overlap first with person B and later with person C on the same date.
      An example of this is the following where your code only calculates the shared time between (1) Lorne and Shaq and (2) Shaq and Frank for a total of (3:00 + 0:30) shared time while the total shared time should be (0:30 + 3:00 + 4:00):
      Frank 02/03/2020 P300027300 9:30 10:30
      Lorne 02/03/2020 P300027300 6:00 9:00
      Shaq 02/03/2020 P300027300 6:00 10:00


      I also cannot seem to apply your code to the full dataset. I get a run-time error '9': subscript out of range with a highlight on the following (Line 28):
      Would you know why that is? I get it when trying both ways: pasting data in your sheet and copying over the code to the OG file...
      Please Login or Register  to view this content.
    • Kaasplankje
      I was able to apply your code to the full set and it took about 20 minutes for it to calculate the total database :D
      The example listed above with Lorne, Shaq and Frank is also not calculated 100% correct in your routine. Shared time of Lorne and Shaq are at 3:00 while Shaq has 1 hour extra shared with Frank

    => The ultimate solution?
    A combination of both? :D
    The array approach from Beyond Excel as this is vastly faster than the approach by Kaasplankje (tested by bsalv for which thanks!)
    The calculation and presantation from Kaasplankje as it is closest to the result I would need for my further calculations and a more robust approach for the multiple conditions that can arise
    + implementation of the possibility that 1 worker has overlapping time with multiple colleagues throughout the day

    it is already crazy what you guys have created here and I'm incredibly thankful for the time invested! Let me know when I need to take my gains and call it a win (which it is already )
    Last edited by sean connerie; 12-05-2022 at 06:45 AM.

  32. #32
    Forum Contributor
    Join Date
    11-23-2022
    Location
    Amsterdam
    MS-Off Ver
    2021
    Posts
    193

    Re: calculate total time a worker has in team on a site on a certain date

    This code addresses the flaw in my code and the point raised in my message yesterday. I have tested it for a case where times for Jambo and Rambo are no longer overlapping on the 2nd of March.

    Please Login or Register  to view this content.
    Calculate team shared time (6).xlsm

  33. #33
    Forum Contributor
    Join Date
    11-23-2022
    Location
    Amsterdam
    MS-Off Ver
    2021
    Posts
    193

    Re: calculate total time a worker has in team on a site on a certain date

    I reckon it can be made a bit faster when the table is sorted both on date and location, but first let me know if this works for you.

  34. #34
    Forum Expert
    Join Date
    01-25-2011
    Location
    Belgium, Alveringem
    MS-Off Ver
    Excel 2003, 2007, 365
    Posts
    1,418

    Re: calculate total time a worker has in team on a site on a certain date

    in column G the hours start-end
    in column H = start & end hours of the common hours of the row x and the co-worker
    in column I = start & end hours when this worker became wo-worker in column H of a previous row

    Now is the question, what do you want to calculate ? for example the rows 54-56. The job is starts with one worker, then 2, 3 and again 2, 1
    Attached Files Attached Files
    Last edited by bsalv; 12-05-2022 at 08:03 AM.

  35. #35
    Forum Contributor
    Join Date
    11-23-2022
    Location
    Amsterdam
    MS-Off Ver
    2021
    Posts
    193

    Re: calculate total time a worker has in team on a site on a certain date

    Just noticed there is still an issue in my code between Shaq and Frank.

  36. #36
    Forum Contributor
    Join Date
    11-23-2022
    Location
    Amsterdam
    MS-Off Ver
    2021
    Posts
    193

    Re: calculate total time a worker has in team on a site on a certain date

    This solves the issue:

    Please Login or Register  to view this content.
    Edit: now with updated DIM list
    Last edited by kaasplankje; 12-05-2022 at 12:07 PM.

  37. #37
    Forum Contributor
    Join Date
    11-23-2022
    Location
    Amsterdam
    MS-Off Ver
    2021
    Posts
    193

    Re: calculate total time a worker has in team on a site on a certain date

    Running this routine 1000 times on the 77 row dataset takes 110s on my laptop.

  38. #38
    Registered User
    Join Date
    11-14-2018
    Location
    Belgium
    MS-Off Ver
    Microsoft office Professional Plus 2019
    Posts
    18

    Re: calculate total time a worker has in team on a site on a certain date

    @kaasplankje, not sure why but with your latest data it does not seem to calculate/find a solution at all.

    Excel bugs out and stays at not responding, I left it running for +1 hour but no result
    I did not try with the 'sort the data first' method, could give that a go tomorrow maybe

    @bsalv id does not matter if they are 2 or 3 or more working together at the same time.
    At least 2 is the only criteria.
    So your rows 54-56 should have following results:
    Vader: 7:00
    Lorne: 7:30
    Robert: 3:30

  39. #39
    Forum Contributor
    Join Date
    11-23-2022
    Location
    Amsterdam
    MS-Off Ver
    2021
    Posts
    193

    Re: calculate total time a worker has in team on a site on a certain date

    Uploading it once again here: Calculate team shared time (6).xlsm

    On my machine at home it finishes the 1000 times loop after 44s, and after disabling application.statusbar in 33s

  40. #40
    Forum Contributor
    Join Date
    11-23-2022
    Location
    Amsterdam
    MS-Off Ver
    2021
    Posts
    193

    Re: calculate total time a worker has in team on a site on a certain date

    If it still fails, disable the timer.

  41. #41
    Forum Expert
    Join Date
    01-25-2011
    Location
    Belgium, Alveringem
    MS-Off Ver
    Excel 2003, 2007, 365
    Posts
    1,418

    Re: calculate total time a worker has in team on a site on a certain date

    100 rounds BSALV = 1.5 sec
    100 rounds Kaasplankje = 6.5 sec

    My column G = total hours = 330 hours
    My Columns H:I = the text is the common start and end hours, the source row and the name of the worker
    In column H = the hours another worker is joining the first worker in this period (there can be several simultatniously)
    In column I = the opposite of the previous a few lines further = the hours that worker isn't working alone, but together with another worker who started earlier.
    The sum of columns H and I are of course equal = 57 common "BSALV-hours" or 17% of the total hours = the sum of the hours of 2nd, 3rd, 4th, ... worker of the team. Normally, you still have to add the hours of the 1st worker, but that's a few extra lines to be added to the code. In case there is only 2nd workers, those hours 'll also be 57 hours, but if there are 3rd workers, it 'll be less.

    There is a conditional format between my solution and the solution of Kaasplankje. In the orange rows, there is a differences to be checked.
    The sum of column O (=Kaasplankje) = 92 hours, but the common hours are double booked, so 92/2 = 46 common "Kaasplankje-hours".
    Attached Files Attached Files
    Last edited by bsalv; 12-05-2022 at 02:52 PM.

  42. #42
    Forum Expert
    Join Date
    01-25-2011
    Location
    Belgium, Alveringem
    MS-Off Ver
    Excel 2003, 2007, 365
    Posts
    1,418

    Re: calculate total time a worker has in team on a site on a certain date

    @sean connerie
    in my row, you see that "Vader" is working 7:30 hours in in those hours there are 10 simultaniously hours, 7 of Frank and 3 of Lorne.
    Frank is working 7:30 hours, 7 of them together with Vader (and Lorne) and the last 0:30 only with Lorne.
    Lorne is working 8 hours, 3:30 of them with Vader and/or Frank

    I don't understand the hours you're suggesting.
    Attached Images Attached Images

  43. #43
    Registered User
    Join Date
    11-14-2018
    Location
    Belgium
    MS-Off Ver
    Microsoft office Professional Plus 2019
    Posts
    18

    Re: calculate total time a worker has in team on a site on a certain date

    @Kaasplanje I let it run for a while and in the end a result did come out! (I think it took about 1h30. Data was presorted)

    Everything seems to check out, just these overlapping shifts to be added together in the total shared time (as discussed above still seems to be a hickup)

    The underneath site return very high shared times (last column), higher than the total shift time.
    Worker ID DATE Site ID STARTTIME ENDTIME
    Jeeanne 01/03/2020 P300503500 08:00:00 15:30:00 14:30
    April 01/03/2020 P300503500 09:00:00 17:00:00 13:30
    Jean 01/03/2020 P300503500 08:00:00 15:00:00 07:00
    Tray 01/03/2020 P300503500 06:00:00 13:00:00 12:00
    Yao 01/03/2020 P300503500 17:00:00 22:00:00 03:00
    Chris 01/03/2020 P300503500 06:00:00 16:00:00 15:30
    Vader 01/03/2020 P300503500 22:00:00 06:00:00
    Mike 01/03/2020 P300503500 18:00:00 21:00:00 03:00
    Frank 01/03/2020 P300503500 08:00:00 16:30:00 15:30

    Could it be that it adds the shared time twice? Once with the first overlap and then the total time + second overlap or something?

    In anycase, it seems that the check to see if there is a shared time throughout his shift is working correctly.
    => I can just add a formula at the end to get the correct time and we are correct no?
    :
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    I think from my side this seems to be as far as we can push it? The only improvement would be to make it faster but, if I understand it correctly, that would mean to rewrite the whole code which I will not ask

    Let me know what you think of that last check to rectify that last hickup and I'll present you a very beautiful "SOLVED"

  44. #44
    Forum Contributor
    Join Date
    11-23-2022
    Location
    Amsterdam
    MS-Off Ver
    2021
    Posts
    193

    Re: calculate total time a worker has in team on a site on a certain date

    @sean: I will have a good look at it. It's relevant, but also a real challenge to take all potential scenarios into account. Give me a few days.

  45. #45
    Forum Contributor
    Join Date
    11-23-2022
    Location
    Amsterdam
    MS-Off Ver
    2021
    Posts
    193

    Re: calculate total time a worker has in team on a site on a certain date

    Here is new code. It has been tested for a dataset including the entries posted at 4:07 today:

    Please Login or Register  to view this content.
    Calculate team shared time (7).xlsm

    It finishes in 31s after a 1000 times loop.

  46. #46
    Forum Contributor
    Join Date
    11-23-2022
    Location
    Amsterdam
    MS-Off Ver
    2021
    Posts
    193

    Re: calculate total time a worker has in team on a site on a certain date

    Still not satisfied. Consider the following case:

    Worker ID DATE Site ID STARTTIME ENDTIME answer
    Jeeanne 1-3-2020 P300503500 08:00:00 15:30:00 03:00
    April 1-3-2020 P300503500 09:00:00 11:00:00 02:00
    Jean 1-3-2020 P300503500 12:00:00 14:00:00 02:00
    Tray 1-3-2020 P300503500 11:00:00 14:00:00 02:00

    Here, the overlapping time of Jeeanne is not correct as April and Jean have overlapping time whilst between them the time is not overlapping. This would accumulate to 4 hours, whilst overlapping time with Tray is only 3.

    Will have a further think tomorrow.

  47. #47
    Forum Expert
    Join Date
    01-25-2011
    Location
    Belgium, Alveringem
    MS-Off Ver
    Excel 2003, 2007, 365
    Posts
    1,418

    Re: calculate total time a worker has in team on a site on a certain date

    or like this
    Attached Files Attached Files

  48. #48
    Valued Forum Contributor
    Join Date
    08-08-2022
    Location
    Buenos Aires
    MS-Off Ver
    Excel 2019
    Posts
    1,777

    Re: calculate total time a worker has in team on a site on a certain date

    I don't know if anyone has noticed...
    However, we are almost 50 posts now and no one knows for sure what @sean connerie wants to achieve.

    So, @sean connerie:

    - In the attached workbook you will see only two sites and few days worked.
    - You must incorporate a second sheet into the workbook in which -by hand- you will put the expected final result.
    - But this time let it be the "expected result" because you are truly responsible for making this exchange stretch unnecessarily due to your doubts, bad explanations and lack of examples.

    If you don't then -I anticipate- you will no longer receive my attention.
    Attached Files Attached Files

  49. #49
    Forum Expert
    Join Date
    01-25-2011
    Location
    Belgium, Alveringem
    MS-Off Ver
    Excel 2003, 2007, 365
    Posts
    1,418

    Re: calculate total time a worker has in team on a site on a certain date

    And perhaps an explanation why starttime and endtime isn't in the range 0-1, so meaning "hours" or like Kaasplankje is treating them in a rather peculiar way, have to be added to the date.
    The attachment is just visual changed
    Attached Files Attached Files
    Last edited by bsalv; 12-07-2022 at 05:10 AM.

  50. #50
    Valued Forum Contributor
    Join Date
    08-08-2022
    Location
    Buenos Aires
    MS-Off Ver
    Excel 2019
    Posts
    1,777

    Re: calculate total time a worker has in team on a site on a certain date

    I am not going to show any macro until OP "does his homework", that is: open the workbook from post #48 and calculate by hand and show us the expected result.

    However I "suspect" that what I am now attaching is what OP is looking for.
    Attached Files Attached Files

  51. #51
    Forum Contributor
    Join Date
    11-23-2022
    Location
    Amsterdam
    MS-Off Ver
    2021
    Posts
    193

    Re: calculate total time a worker has in team on a site on a certain date

    I have come to realize that at some point I was wrong in my thinking of how to approach this. I also see that you guys are so miuch more experienced than I.

    I encourage Sean to come back to us. I can imagine he feels a bit overwhelmed with the possibilities and cases that he initially did not consider.

  52. #52
    Registered User
    Join Date
    11-14-2018
    Location
    Belgium
    MS-Off Ver
    Microsoft office Professional Plus 2019
    Posts
    18

    Re: calculate total time a worker has in team on a site on a certain date

    Hi all,

    Indeed, my post has gone far further than what I would have imagined :o

    @beyond Excel, I've attached your workbook here with a calculation by hand of what the correct/intended shared time should be in column I
    I've added as well the other calculations I will apply to get the final result I am looking for => % of time in a month that a worker has spend in team

    Indeed the multiple possibilities that are arising in this request are bigger than initially envisioned.

    But I feel that, from what I can gather, Kaasplankje is very close to a solution and that he understands what I'm looking for...
    It's the time it takes for the calculations to run on the full dataset and the difficulty of overlapping shifts which makes you say that the 'approach' what not the best?

    @bsalv the format of the start and end time is as such in the original dataset. It is not at all required to stay this way. I had changed this to a normal hour format with the Text-to-columns function on my formula based try before contacting you.
    Unfortunatly I forgot to include this in my initial request, did not think that I would have an influence.


    So in general,I'm not really looking for a nice macro with epic layouts that show an overview of who is sharing time with who ect.
    It is only an intermediate step toward a final result which is a a month overview for each worker

    Hope this clarifies my needs a bit better, apologies if I was unclear in my requests and answers until now.
    Attached Files Attached Files

  53. #53
    Forum Contributor
    Join Date
    11-23-2022
    Location
    Amsterdam
    MS-Off Ver
    2021
    Posts
    193

    Re: calculate total time a worker has in team on a site on a certain date

    Thanks for your reply. Still I hope that someone else could nail this as I feel less competent.

  54. #54
    Forum Expert
    Join Date
    01-25-2011
    Location
    Belgium, Alveringem
    MS-Off Ver
    Excel 2003, 2007, 365
    Posts
    1,418

    Re: calculate total time a worker has in team on a site on a certain date

    it's just a small modification on previous result
    Attached Files Attached Files

  55. #55
    Valued Forum Contributor
    Join Date
    08-08-2022
    Location
    Buenos Aires
    MS-Off Ver
    Excel 2019
    Posts
    1,777

    Re: calculate total time a worker has in team on a site on a certain date

    Quote Originally Posted by sean connerie View Post
    It is only an intermediate step toward a final result which is a a month overview for each worker.
    The sentence that I reply here highlights what has been happening:

    - The consultation began looking for a monthly statistic regarding each 'site'.

    - And now it is said that statistics should focus on each individual worker.

    As what I have shown in post #50 is -exactly- what was initially requested and OP has never thanked the proposals by clicking on the "*" that is located at the bottom-left of the messages, so I have decided to interrupt my participation here .

    Greetings and good luck.

  56. #56
    Forum Contributor
    Join Date
    11-23-2022
    Location
    Amsterdam
    MS-Off Ver
    2021
    Posts
    193

    Re: calculate total time a worker has in team on a site on a certain date

    You nailed it, BSALV. Well done, very impressive!

  57. #57
    Registered User
    Join Date
    11-14-2018
    Location
    Belgium
    MS-Off Ver
    Microsoft office Professional Plus 2019
    Posts
    18

    Re: calculate total time a worker has in team on a site on a certain date

    Hi bsalv,
    Indeed, as stated by Kaasplank, you nailed it!
    I have not come accross any more errors, nor minor miscalulations or whatnot!
    - nightshifts are calculated correctly
    - overlaps are counted for
    - ...

    a million thanks, I thought I had unlimited Reputation (after Beyond Excel pointed me towards it) but unfortunatly not and I spend it all on the first few posts lol




    Now on how long it takes for the full dataset:

    When copying over the total data (52K lines) I get an error:
    runtime error 13 type mismatch which debugs to => line 52: For j = r(4) To r(5) - 1

    Seems to be purely linked to the number of lines from what I gather because when I decrease the data it runs smoothly and with the following timers:
    30K lines: 696s and collection data 695s
    39K lines: 1220s
    49K lines: error
    53K lines: error

    Could it be that the variable/dimension used has a limited space with which it can work?
    Is there an easy fix to this or what that influence the full code?
    IF NO: then please let me know and I'll just split the data into multiple excersices!! This last thing is pure convenience lol

    thanks again!

  58. #58
    Registered User
    Join Date
    11-14-2018
    Location
    Belgium
    MS-Off Ver
    Microsoft office Professional Plus 2019
    Posts
    18

    Re: calculate total time a worker has in team on a site on a certain date

    Hi all,

    seems I pushed it as far as possible thank you very much for the help on this post. I have learned a lot!

    greetz, I'll put the post as solved now!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Need formula to calculate count of total team task month,week,day wise
    By johnlara in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-20-2022, 09:54 AM
  2. Need to return the worker site, based on a table with his working hours
    By Bruno Trafaria in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 09-22-2022, 09:31 PM
  3. Vlookup to calculate the total per site
    By gcolegcole42 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-10-2021, 04:15 AM
  4. Calculate total hours worked in week by worker name
    By evgemans in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 06-23-2019, 12:24 PM
  5. Replies: 3
    Last Post: 12-19-2013, 06:49 AM
  6. Replies: 5
    Last Post: 08-02-2010, 07:25 PM
  7. Calculate total Hours between 2 date and time
    By Tylim in forum Excel General
    Replies: 3
    Last Post: 05-08-2006, 10:20 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1