Find my missing time

[gkbrenne]

I am hoping someone can help me out. I am using a program to track my time used on projects (Toggl), but I have found that I have some missing time blocks. Instead of finding the time blocks manually, I'd like to use Excel to do this.

The spreadsheet from Toggl looks like this
[TABLE="width: 716"]
<tbody>[TR]
[TD]Description[/TD]
[TD]Billable[/TD]
[TD]Start date[/TD]
[TD]Start time[/TD]
[TD]End date[/TD]
[TD]End time[/TD]
[TD]Duration[/TD]
[/TR]
[TR]
[TD]PM: Nodal[/TD]
[TD]No[/TD]
[TD="align: right"]8/18/2017[/TD]
[TD="align: right"]7:10:15[/TD]
[TD="align: right"]8/18/2017[/TD]
[TD="align: right"]7:31:52[/TD]
[TD="align: right"]0:21:37[/TD]
[/TR]
[TR]
[TD]Quickbooks/Process review[/TD]
[TD]No[/TD]
[TD="align: right"]8/18/2017[/TD]
[TD="align: right"]7:31:53[/TD]
[TD="align: right"]8/18/2017[/TD]
[TD="align: right"]11:54:56[/TD]
[TD="align: right"]4:23:02[/TD]
[/TR]
[TR]
[TD]First draft-requirements[/TD]
[TD]No[/TD]
[TD="align: right"]8/18/2017[/TD]
[TD="align: right"]12:30:32[/TD]
[TD="align: right"]8/18/2017[/TD]
[TD="align: right"]13:16:45[/TD]
[TD="align: right"]0:46:13[/TD]
[/TR]
[TR]
[TD]Lv1 Phone Support[/TD]
[TD]No[/TD]
[TD="align: right"]8/18/2017[/TD]
[TD="align: right"]13:57:48[/TD]
[TD="align: right"]8/18/2017[/TD]
[TD="align: right"]14:27:48[/TD]
[TD="align: right"]0:30:00[/TD]
[/TR]
[TR]
[TD]CRM Cleanup[/TD]
[TD]No[/TD]
[TD="align: right"]8/18/2017[/TD]
[TD="align: right"]14:41:49[/TD]
[TD="align: right"]8/18/2017[/TD]
[TD="align: right"]17:14:42[/TD]
[TD="align: right"]2:32:53[/TD]
[/TR]
</tbody>[/TABLE]

Can anyone think of a way that I can find a gap in my time entries using VBA or formulas? My ideal results from above would be:
8/18/2017 11:55:57 8/18/17 12:30:31 0:34:34
8/18/2017 13:16:46 8/18/17 13:57:47 0:41:01

I don't have to be that accurate, but you get the idea.

Any help would be appreciated!

 

[piotrekp1]

are these always sorted? I mean can I assume that PM:Nodal is always before Quickboooks, etc.?

 

[piotrekp1]

I8 : =IF($F9+TIME(0,0,1)=$D10,"", $F9+TIME(0,0,1))
J8 : =IF($F9+TIME(0,0,1)=$D10,"", $D10-TIME(0,0,1))

And you drag them down. They leave cell empty if there's no break, and they fill them if there is. Cheers

 

[MrTeeny]

If we assume the data you posted covers A1 :G6 some VBA like this might work to fill in the gaps

Sub findTime()
On Error GoTo xit:
Application.ScreenUpdating = False
Application.Calculation = xlCalculateManual
Dim FinalRow As Integer
Dim startTime As Date
Dim endTime As Date


  FinalRow = Cells(Rows.Count, "C").End(xlUp).Row




    For i = FinalRow To 3 Step -1
    startTime = TimeValue(Range("F" & i - 1).Text) + TimeValue("00:00:01")
    endTime = TimeValue(Range("D" & i).Text) - TimeValue("00:00:01")
    If startTime - endTime < 0 Then


    Range("A" & i & ":G" & i).Insert (xlShiftDown)
    Range("C" & i & ",E" & i).Value = Range("C" & i + 1).Value
    Range("A" & i).Value = "MISSING TIME"
    Range("D" & i).Value = startTime
    Range("F" & i).Value = endTime
    Range("G" & i).Value = endTime - startTime
    End If
    Next i
  
xit:
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

 

[gkbrenne]

Thank you piotrekp1 for getting me going! I made the mistake of including only one day of entries -- this will go on and on through the month. Also, I don't have to be exact to the second -- just the minute. But as with all good solutions, I was able to re-engineer it.

I8 now looks like: =TEXT($K8+TIME(0,0,1),"h:mm")
J8 : =TEXT($I9-TIME(0,0,1),"h:mm")

In K8 I have it calculate the gap and if it is less than 1 minute or are different days, I do not have it display anything.
k8 : =IF(E8=C9,IF(J8=I8,"",J8-I8),"")

[TABLE="width: 967"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[/TR]
[TR]
[TD]Description[/TD]
[TD]Billable[/TD]
[TD]Start date[/TD]
[TD]Start time[/TD]
[TD]End date[/TD]
[TD]End time[/TD]
[TD]Duration[/TD]
[TD]Date[/TD]
[TD]GapStart[/TD]
[TD]GapEnd[/TD]
[TD]GapDuration[/TD]
[/TR]
[TR]
[TD]Meet ken[/TD]
[TD]No[/TD]
[TD="align: right"]8/1/2017[/TD]
[TD="align: right"]8:00[/TD]
[TD="align: right"]8/1/2017[/TD]
[TD="align: right"]8:30[/TD]
[TD="align: right"]0:30:00[/TD]
[TD="align: right"]8/1/2017[/TD]
[TD]8:30[/TD]
[TD]8:30[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Creston[/TD]
[TD]No[/TD]
[TD="align: right"]8/1/2017[/TD]
[TD="align: right"]8:30[/TD]
[TD="align: right"]8/1/2017[/TD]
[TD="align: right"]13:28[/TD]
[TD="align: right"]4:58:00[/TD]
[TD="align: right"]8/1/2017[/TD]
[TD]13:28[/TD]
[TD]13:29[/TD]
[TD="align: right"]0:01[/TD]
[/TR]
[TR]
[TD]Lunch[/TD]
[TD]No[/TD]
[TD="align: right"]8/1/2017[/TD]
[TD="align: right"]13:29[/TD]
[TD="align: right"]8/1/2017[/TD]
[TD="align: right"]13:29[/TD]
[TD="align: right"]0:00:00[/TD]
[TD="align: right"]8/1/2017[/TD]
[TD]13:29[/TD]
[TD]18:10[/TD]
[TD="align: right"]4:41[/TD]
[/TR]
[TR]
[TD]Home & Away[/TD]
[TD]No[/TD]
[TD="align: right"]8/1/2017[/TD]
[TD="align: right"]18:10[/TD]
[TD="align: right"]8/1/2017[/TD]
[TD="align: right"]18:10[/TD]
[TD="align: right"]0:00:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AgWestern[/TD]
[TD]No[/TD]
[TD="align: right"]8/2/2017[/TD]
[TD="align: right"]11:00[/TD]
[TD="align: right"]8/2/2017[/TD]
[TD="align: right"]11:24[/TD]
[TD="align: right"]0:24:46[/TD]
[TD="align: right"] 8/2/2017[/TD]
[TD]11:24[/TD]
[TD]11:30[/TD]
[TD="align: right"]0:06[/TD]
[/TR]
</tbody>[/TABLE]

(For clarity sake, I removed the IF statement that checks if the date is different from the above formulas)

I now have a quick way to see what other time entries I need to make.