Sunday, May 24, 2020

Excel Timesheet Tracker

Excel Timesheet Tracker helps businesses monitor employee’s time spent on assigned tasks and sub-tasks. This version captures daily entries into a worksheet, where open items can be exported on demand.  Excel Timesheet Tracker is a simple but useful tool, and it can be adapted for weekly entries easily.


Timesheet Console

The main console of Excel Timesheet Tracker records time spent on tasks and sub-tasks for a particular user. Each employee should keep a copy of this file of his own, either in his system or a shared location for a manager to supervise. Then, entries can be exported as needed (that’s something each company may agree: i.e. weekly, monthly, on project terms, etc), as a separate spreadsheet that can be combined with those of other employees and stored separately for management purposes.



User Form Entries

User tasks, sub-tasks, and any other time to be recorded will be entered through a VBA UserForm in Excel Timesheet Tracker. The three available fields are populated with data from the TaskList worksheet.



This is done with the UserForm_Initialize subroutine by looping through the number of rows with data (lr) for each category (Tasks, Sub-tasks, Other), and adding each item to a ComboBox in UserForm1 (this is the default name, but it can be changed). See the example below for Tasks, and note the ComboBox for Tasks is named “Tasks” (same for sub-tasks and other).


  Dim item As String, r As Integer, lr As Integer
  Dim TaskList As Worksheet
  Set TaskList = ThisWorkbook.Sheets(“TaskList”)
   
  ‘Tasks
    lr = TaskList.Cells(Rows.Count, “A”).End(xlUp).Row
    For r = 2 To lr
        item = TaskList.Cells(r, 1).Value
        Tasks.AddItem item
    Next r


The ‘Add’ button in the user form will add the selected items to the main console worksheet via the following VBA code (example for Task):


  newTask = UserForm1.Tasks.Text
  lr = cons.Cells(Rows.Count, “B”).End(xlUp).Row
  cons.Range(“B” & lr + 1 & “:E” & lr + 1).Value = newTask


Show Timesheet Entries

Excel Timesheet Tracker checks if entries were already added for a given date and pops up a warning if so before saving. The user can click ‘Show’ to check the entries for that given date in the “Entries” worksheet. Entries appear as “Open” until they are exported, in order to track timesheet submission to the required recipient. When exported, the status changes to “Closed”. See below an example of timesheet entries.



In both SaveTS and ShowTS procedures the date is taken from three separate cells in the console worksheet, added together and formatted into a date variable (DateTS):


  Dim DMYTS As String
  Dim DateTS As Date

  DMYTS = DayTS & "/" & MonthTS & "/" & YearTS
  DateTS = Format(DMYTS, "dd/mm/yyyy")


There are many different ways to input dates though. New versions of Excel have a cool calendar functionality to add dates by picking from a calendar.

Export Timesheet Entries

All open timesheet entries in Excel Timesheet Tracker can be exported to a separate (macro-free) workbook. Those exports could later easily be put together for a whole team or company to track timesheets.

The macro is set to add and save a new workbook in the same drive location where the main Excel Timesheet Tracker is stored using the property “Path”. It names the timesheet exported file after user’s name and surname, and current date, and if the file does not exists will add the new workbook.


  Dim wb1 As Workbook, wb2 As Workbook, TS As Worksheet
  Dim uName As String, uDate as Variant
  Dim drivePath As String, TimesheetWB As String
  Dim OverWriteFile As VbMsgBoxResult

  Set wb1 = ThisWorkbook

  uName = cons.Range("C4").Value
  uDate = Format(Date, "ddmmyyyy")

  drivePath = wb1.Path

  TimesheetWB = drivePath & "\Timesheet_" & uName & "_" & uDate & ".xlsx"
  If Dir(TimesheetWB) <> "" Then
      OverWriteFile = MsgBox("File already exists. Do you want to overwrite?" _
      , vbYesNo)
      If OverWriteFile = vbYes Then
          Workbooks.Open (TimesheetWB)
          Set wb2 = ActiveWorkbook
          Set TS = Sheets("Timesheet")
          TS.Cells.Clear
      Else
          Exit Sub
      End If
  Else
      Set wb2 = Workbooks.Add
      ActiveSheet.Name = "Timesheet"
      wb2.SaveAs Filename:=TimesheetWB
  End If



Click Here to download the Excel Timesheet Tracker.

No comments:

Post a Comment