How to summarize time in/time out data to see employee hours worked

[Casey Mims]

Hi all,

I have been given a rather large output file that I'd like help with summarizing. The file lists when an employee has clocked in and out of the building. I'd like to be able to summarize time actually spent in the building per day, per week, etc. The formatting of the file is a little awkward as there are skipped rows, and the entry/exit descriptions are specific to the specific doorway scanned.

Could someone help me with this? I have very limited experience with pivot tables. Would that be appropriate here? If so, does anyone have suggestions for how to handle the formatting to make the file more conducive to a pivot table? I am working with Excel 2016.

[#2]

pls see attached file.

hope this could help.

thanks..

[#3]

Thank you Lorenzo, I was able to make much progress with your help.

Attached is an abbreviated version of what I have now. I'd like to be able to summarize the # hrs spent in the building per day. What is the best way to go about this? My actual file is quite long, and it's not conducive to simply sum specific cell ranges manually. Please help!

[#4]

I hope the attached file can help you.

thanks..

[#5]

Thanks again Lorenzo!

For the first column, is there an easier way to fill in the dates on each row besides manually? My actual file has >1000 rows, and there are 5 files. Yikes!

Also, is there a way I can autofill the formula in Column E down the rows? When I autofill currently, it puts the sum on each row. I'd like the sum to only show up on the row where a new date first appears in Column A, like in your attached example.

[#6]

post this under a new title and attach the file.
explain what you need.
there are many good people here that may help you.
thanks..

[#7]

here is a VBA to help you fill down the formula from C3 down to the last row.

try this on a test sample - save as macro enabled. (.xlsm)

hope this helps

thanks..

Sub FillDownFromC3()
        Application.ScreenUpdating = False 
    mlrc = Cells(Rows.Count, "B").End(xlUp).Row
    Range("C3:C" & mlrc).FillDown
    Application.ScreenUpdating = True 
End Sub

[#8]

press ALT F8 to open Macro Box then Run it.

[#9]

pls see attached file.

I provided a temp sheet for you to test.

first filldown C3 then E2

hope you can work something out of this.

thanks..