Forum Discussion

Copper Contributor

Oct 03, 2018

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

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

40843_short.xls38 KB

excel

Formulas and Functions

Casey Mims

Copper Contributor

Oct 05, 2018

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.

Lorenzo Kim

Bronze Contributor

Oct 06, 2018

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

Lorenzo Kim
Bronze Contributor
Oct 06, 2018
press ALT F8 to open Macro Box then Run it.