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

Description	Billable	Start date	Start time	End date	End time	Duration
PM: Nodal	No	8/18/2017	7:10:15	8/18/2017	7:31:52	0:21:37
Quickbooks/Process review	No	8/18/2017	7:31:53	8/18/2017	11:54:56	4:23:02
First draft-requirements	No	8/18/2017	12:30:32	8/18/2017	13:16:45	0:46:13
Lv1 Phone Support	No	8/18/2017	13:57:48	8/18/2017	14:27:48	0:30:00
CRM Cleanup	No	8/18/2017	14:41:49	8/18/2017	17:14:42	2:32:53

<tbody>
</tbody>

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),"")

A	B	C	D	E	F	G	H	I	J	K
Description	Billable	Start date	Start time	End date	End time	Duration	Date	GapStart	GapEnd	GapDuration
Meet ken	No	8/1/2017	8:00	8/1/2017	8:30	0:30:00	8/1/2017	8:30	8:30
Creston	No	8/1/2017	8:30	8/1/2017	13:28	4:58:00	8/1/2017	13:28	13:29	0:01
Lunch	No	8/1/2017	13:29	8/1/2017	13:29	0:00:00	8/1/2017	13:29	18:10	4:41
Home & Away	No	8/1/2017	18:10	8/1/2017	18:10	0:00:00
AgWestern	No	8/2/2017	11:00	8/2/2017	11:24	0:24:46	8/2/2017	11:24	11:30	0:06

<tbody>
</tbody>

(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.