On some days a worker multitasks. How do I account for overlapping tasks?

[timmy2]

Here's a worker's activity log. I am interested in adding up "minutes worked", but only minutes that do not overlap. In other words, multi-tasking is not rewarded, per se.

Task	Work started	Work ended	Minutes worked	Date
A	5:00PM	6:15PM	75	1/13/2017
A	2:00PM	4:00PM	120	1/15/2017
B	2:10PM	2:20PM	10	1/15/2017
C	2:12PM	2:17PM	5	1/15/2017
D	3:00PM	5:00PM	120	1/15/2017
A	1:00PM	2:00PM	60	1/17/2017

<tbody>
</tbody>

On some days he performs a single task so all the minutes count. (see 1/13 and 1/17)

But on some days he performs more than one task, like on 1/15. When he logs in he starts the first task of the day. Then he starts another task, which might be short (like task B) or it might extend beyond the end of the first task (like task D).

Given the rules I can't simply add up the minutes column.

In a new column I have tried numerous formulas using IF statements to either count the full minutes worked or reduce or eliminate them if they occur while other tasks are already being performed. If this were a program I could use a variable to compare to but doing this in Excel baffles me.

BTW, the worker is me, so don't feel sorry for him.

 

[admiral100]

Hi,

So for 1/15 how many minutes / hours should be ? 180 minutes?

 

[Perpa]

timmy2,
Assuming '180' is the total you are trying to get on 1/15/2017, here is a macro solution.
The Times must be in 'Time format', meaning there needs to be is a space between the minutes and the 'PM',
and Excel recognizes them as 'Time', not as a 'General' number or 'Text'.
Perpa

Sheet1

	A	B	C	D	E	F
1	Task	Work started	Work ended	Minutes worked	Date	Total Minutes Worked
2	A	5:00 PM	6:15 PM	75	1/13/2017	75
3	A	2:00 PM	4:00 PM	120	1/15/2017	180
4	B	2:10 PM	2:20 PM	10	1/15/2017
5	C	2:12 PM	2:17 PM	5	1/15/2017
6	D	3:00 PM	5:00 PM	120	1/15/2017
7	A	1:00 PM	2:00 PM	60	1/17/2017	60

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Sub AccumTime()
Dim TaskA, rw, LastRow As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For TaskA = 2 To LastRow
    If Cells(TaskA, "A") = "A" Then
        Cells(TaskA, "F") = Cells(TaskA, "D")
        For rw = TaskA + 1 To LastRow
           If Cells(rw, "A") = "A" Then GoTo NextA    'go to next Task A
           If Cells(rw, "E") = Cells(TaskA, "E") Then   'Same Day
              If Cells(rw, "B") >= Cells(TaskA, "B") And Cells(rw, "C") <= Cells(TaskA, "C") Then GoTo Nextrw  'No Additional time
        
              If Cells(rw, "B") >= Cells(TaskA, "C") Then Cells(TaskA, "F") = Cells(TaskA, "F") + Cells(rw, "D") 'Additional Time after Original Task Ends
        
              If Cells(rw, "B") >= Cells(TaskA, "B") And Cells(rw, "C") > Cells(TaskA, "C") Then 'Additional Time after Original TaskA Ends
                  Cells(TaskA, "F") = Cells(TaskA, "F") + (Cells(rw, "C") - Cells(TaskA, "C")) * 1440
              End If
           End If
Nextrw:
        Next
    End If
NextA:
Next
End Sub

 

[timmy2]

Thank you for the reply! And you are correct about the 180.


Task A was from 2 to 4pm, so the 120 minutes there covers my presence until 4pm. Therefore tasks B and C are unchargable minutes. In a "Net Time" column I would put 0 in those rows. As to task D, the first hour is paid for by task A's minutes, leaving the 60 minutes of work done from 4 to 5pm as chargable. Therefore, the total Net Time for 1/15 would add up to 180 minutes.


Note that this example is a tiny part of the total log, so sometimes Task A is short, followed by a longer task that overlaps several subsequent tasks.

 

[timmy2]

Wow, thank you Perpa. I gotta try your macro as soon as I can. Your contribution is much appreciated.

 

[admiral100]

And a formula version .....

Ctrl+Shift+Enetr Not just Enter

D2 =(MAX(IF($E$2:$E$7=E2,($C$2:$C$7)))-MIN(IF($E$2:$E$7=E2,($B$2:$B$7))))*1440

	A	B	C	D	E
1	Task	Work started	Work ended	Minutes worked	Date
2	A	5:00 PM	6:15 PM	75	1/13/2017
3	A	2:00 PM	4:00 PM	180	1/15/2017
4	B	2:10 PM	2:20 PM	180	1/15/2017
5	C	2:12 PM	2:17 PM	180	1/15/2017
6	D	3:00 PM	5:00 PM	180	1/15/2017
7	A	1:00 PM	2:00 PM	60	1/17/2017

<colgroup><col span="4"><col><col></colgroup><tbody>
</tbody>

 

[timmy2]

timmy2,
Assuming '180' is the total you are trying to get on 1/15/2017, here is a macro solution.
The Times must be in 'Time format', meaning there needs to be is a space between the minutes and the 'PM',
and Excel recognizes them as 'Time', not as a 'General' number or 'Text'.
Perpa

Sheet1

	A	B	C	D	E	F
1	Task	Work started	Work ended	Minutes worked	Date	Total Minutes Worked
2	A	5:00 PM	6:15 PM	75	1/13/2017	75
3	A	2:00 PM	4:00 PM	120	1/15/2017	180
4	B	2:10 PM	2:20 PM	10	1/15/2017
5	C	2:12 PM	2:17 PM	5	1/15/2017
6	D	3:00 PM	5:00 PM	120	1/15/2017
7	A	1:00 PM	2:00 PM	60	1/17/2017	60

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Sub AccumTime()
Dim TaskA, rw, LastRow As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For TaskA = 2 To LastRow
    If Cells(TaskA, "A") = "A" Then
        Cells(TaskA, "F") = Cells(TaskA, "D")
        For rw = TaskA + 1 To LastRow
           If Cells(rw, "A") = "A" Then GoTo NextA    'go to next Task A
           If Cells(rw, "E") = Cells(TaskA, "E") Then   'Same Day
              If Cells(rw, "B") >= Cells(TaskA, "B") And Cells(rw, "C") <= Cells(TaskA, "C") Then GoTo Nextrw  'No Additional time
        
              If Cells(rw, "B") >= Cells(TaskA, "C") Then Cells(TaskA, "F") = Cells(TaskA, "F") + Cells(rw, "D") 'Additional Time after Original Task Ends
        
              If Cells(rw, "B") >= Cells(TaskA, "B") And Cells(rw, "C") > Cells(TaskA, "C") Then 'Additional Time after Original TaskA Ends
                  Cells(TaskA, "F") = Cells(TaskA, "F") + (Cells(rw, "C") - Cells(TaskA, "C")) * 1440
              End If
           End If
Nextrw:
        Next
    End If
NextA:
Next
End Sub

Thank you Perpa for quite a bit of effort and thought. I am humbled as well as embarrassed.

Your macro appears to rely on the presence of column A (lettered tasks), which I added to the example only to help clarify my question. Column A does not exist in the actual 500 line worksheet, nor would I know how to automatically generate it. Worse yet, while I have used Excel for a long time and for fairly complicated projects, the extent to which I have used Macros is limited to automating tasks. I have no idea how to incorporate your VBA code into my worksheet. I apologize for not being up to the task. I would love to see it work!

 

[timmy2]

And a formula version .....

Ctrl+Shift+Enetr Not just Enter

D2 =(MAX(IF($E$2:$E$7=E2,($C$2:$C$7)))-MIN(IF($E$2:$E$7=E2,($B$2:$B$7))))*1440

	A	B	C	D	E
1	Task	Work started	Work ended	Minutes worked	Date
2	A	5:00 PM	6:15 PM	75	1/13/2017
3	A	2:00 PM	4:00 PM	180	1/15/2017
4	B	2:10 PM	2:20 PM	180	1/15/2017
5	C	2:12 PM	2:17 PM	180	1/15/2017
6	D	3:00 PM	5:00 PM	180	1/15/2017
7	A	1:00 PM	2:00 PM	60	1/17/2017

<tbody>
</tbody>

Admiral100, when I apply your array formula to a small 18 row subset of the actual 500 row worksheet, the "minutes worked" results are wrong for dates where the worker multitasked, like 1/7/2017 below. I also do not understand the reasoning behind the IF statements testing the entire range of the date column (E).

 

[Perpa]

[QUOTE I have no idea how to incorporate your VBA code into my worksheet. I apologize for not being up to the task. I would love to see it work!
[/QUOTE]

Timmy2,
Assuming you want to try a macro...
The following code will provide daily totals in column E as shown below...

Sheet2

	A	B	C	D	E
1	Work started	Work ended	Minutes worked	Date	Total Minutes Worked
2	5:00 PM	6:15 PM	75	1/13/2017	75
3	2:00 PM	4:00 PM	120	1/15/2017	180
4	2:10 PM	2:20 PM	10	1/15/2017
5	2:12 PM	2:17 PM	5	1/15/2017
6	3:00 PM	5:00 PM	120	1/15/2017
7	1:00 PM	2:00 PM	60	1/17/2017	60

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 94px;"><col style="width: 78px;"><col style="width: 75px;"><col style="width: 93px;"><col style="width: 103px;"></colgroup><tbody>
</tbody>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4


Please TEST the code FIRST in a COPY of your workbook (always make a backup copy before trying
new code, you never know what you might lose).
How to add code to your workbook:
1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. From the menu, click Insert > Module
5. Paste the code onto the window that opens, then close the window.
6. Before you use the macro, Save As a macro enabled workbook.

To run the macro from Excel:
Press ALT + F8 to display the Run Macro Dialog Box, then
select the macro's name 'AccumTime2', then select 'RUN'.
Have fun!
Perpa

Sub AccumTime2()
Dim rw, rw1, LastRow As Long
LastRow = Cells(Rows.Count, "D").End(xlUp).Row
Cells(2, "E") = Cells(2, "C")
For rw = 3 To LastRow
    If Cells(rw - 1, "D") <> Cells(rw, "D") Then Cells(rw, "E") = Cells(rw, "C")
Next
n = 0
For rw1 = 3 To LastRow
    If Cells(rw1, "E") <> "" Then
        n = n + 1
        GoTo Nextrw
    End If
    
    If Cells(rw1, "E") = "" Then   
        
        If Cells(rw1 - n, "E") <> "" Then
        
            If Cells(rw1, "A") >= Cells(rw1 - n, "A") And Cells(rw1, "B") <= Cells(rw1 - n, "B") Then
                n = n + 1
                GoTo Nextrw     'No Additional time
            End If
            
            If Cells(rw1, "A") >= Cells(rw1 - n, "B") Then Cells(rw1 - n, "E") = Cells(rw1 - n, "E") + Cells(rw1, "C")     'Additional Time if after Original Task Ends (Entire)
            If Cells(rw1, "A") >= Cells(rw1 - n, "A") And Cells(rw1, "B") > Cells(rw1 - n, "B") Then                  'Additional Time if End after original End (Partial)
                Cells(rw1 - n, "E") = Cells(rw1 - n, "E") + (Cells(rw1, "B") - Cells(rw1 - n, "B")) * 1440
            End If
        End If
    End If
    n = n + 1
Nextrw:
    
Next
End Sub

 

[timmy2]

Perpa, your instructions helped immensely! I was able to reproduce the results shown in your reply.

However, when I pasted a section of real data into a second sheet of the new macro-enabled workbook, and ran the macro the results come out incorrect. For example, the three sessions on 1/7/2017 do not overlap so all should be counted in the total minutes worked. What might I be doing wrong?