Time Tracking Sheet in Excel and VBA

Time Tracking Sheet and it’s advantage

Tracking time in a spreadsheet is really important to keep the records of the time, employees have spent on a particular task or project. Time Tracking sheet provides clear, clean, and easily accessible data for further use. You can utilize this data for different purposes e.g. HR, Payroll, Client Billing and project status reports you send to clients.

Understanding the Time Tracking Sheet Template

Before moving ahead and start developing this Time Tracking Sheet in Excel and VBA, let us discuss the functionalities and restrictions.

In this time tracking sheet, we have utilized two different worksheets. One is TimeSheet which is the main sheet and other is Support Sheet to keep the drop-down data for Project and Task Name column available in TimeSheet worksheet.

In TimeSheet, we are capturing the user name in cell B4. If user will open this tracker or reset it, VBA code will check whether any name is available or not. If name is not available, then it will update the current logged in user name in cell B4.

There are 9 different column available in TimeSheet tracker with headings Date, Project Name, Task Name, Start Time, End Time, Breaks, Actual Time and Remarks.

The first column, date will be updated by system itself if user will open the file, reset the tracker or click on End Time button. If previous activity is pending then it will not update the date.

User needs to select Project Name and Task name from the drop-down. Both the drop-down are connected with the support data sheet and fully dynamic.

In support sheet, we have two different lists one is for Project and other is for Task. Both the lists are dynamic hence if you will append, update or delete any of the data from the lists, Excel formula will update the range automatically and assigned it to drop-down list available in TimeSheet.

In TimeSheet, Start, End, Total Time and Actual Time will be captured or calculated by Excel itself.

If user has selected the Project Name and Task Name from the drop-down, then you can click on ‘Start’ button to capture the Start time for a particular task.

Once user finishes the task then he/she can click on ‘End’ button to capture the End Time, calculate the Total Time and Actual Time. Here, actual time is after deducting the break time, if user has mentioned it manually.

In addition to Start and End button, there is one more button to reset the TimeSheet data.
If user will click on Reset button then it will take confirmation to proceed and delete the data and reset the form. If user will click on Yes button in confirmation window then it will reset the form.

Here, cell protection has been applied on Start, End and Total Time columns. Hence, user will not be able to make any modification or enter the data manually in these three columns.

Actual End Time column will contain the Excel formula; hence user can enter the break time at any point of time to recalculate the actual time taken for a specific task.

The last column is to put the remarks related with a particular task, if required. Here, Break and Remarks are not the mandatory columns. User can leave it blank and update it later on while doing some analysis on time sheet data.

On top of the column headers, there are summary section where aggregated Total Time, Break and Actual Hours are showing. It will help user in tracking overall hours for a particular day.  

Apart from these functionalities, there are some restrictions applied on TimeSheet. If Project and Task name are blank, then user can’t capture the Start Time. It will prompt an error alert and highlight the cells.

If you have started the time and then you are trying to close the Excel file, then it will prompt an error message to capture the End time first then on close.

If an activity is going on and suddenly Excel will get closed due to any reason and then, if user starts Time Tracking Sheet, then it will prompt an alert message that ‘There is an open task that yet to be completed.’.

So, this is all the structure, features and restriction applied on Time Tracking Sheet. If you want to download the practice file, then you can download it from the link provided in the last of this post.

Developing an automated Time Tracking Sheet in Excel and VBA from scratch.

Watch the Step-by-step YouTube tutorial

Please click on below button to download the Time Tracking Sheet. The password for Workbook and Worksheet is four times zero (0000). Don’t forget to update the new password in VBA code, if you are going to change the worksheet protection password.