Work Hours Tracker 2015

Before I build Flickerbook web app I used Excel to track my work hours and pay. I would store one years data in a single excel file. Example of this Excel file for 2015 can be found here.

How it works

The file consists of 10 sheets: first sheet is named “Averages” and the remaining 9 contain data for weekly work hours and payments.

Excel file sheet structure.

In the first sheet named “Averages” I store data for payments and deductions.

Payments and deductions details.

These values for payments and deductions are used to calculate payments in other Excel sheets on this file:

Formula to calculate basic hours pay.

In order to calculate payments like basic hours or overtime pay, there are fields created to enter start and finish times for work day. Once these times are provided, a field below calculates hours worked.

Calculating hours worked.

Results of these calculations appear in these fields:

  • Hours
  • Gross Pay
  • Net Pay
  • Days Worked
Calculated payments and work hours

In the picture above the fields marked in yellow indicate, that this data was entered manually from payslip. This is done in order to be able to compare the calculated values with the actual values in payslip and spot discrepancies.

At the bottom of every sheet that calculates work hours and pay, there is a table that sums this data:

Sum of hours, days and payments for 6 weeks.

As I split sheets to store 6 weeks worth of data in each of it, I then use this summed data in “Averages” sheet to calculate total earnings for the whole tax year.

Calculating earnings for tax year.

Once I have data for payments, hours and days worked I can calculate averages. This is done at the bottom of “Averages” sheet.

Calculated daily and hourly averages.

This Excel file helped me track my hours and payments. It turned out very helpful once I developed Flickerbook, as I was able to upload all the work hours data to Flickerbook from them.

Problems it helped to solve

This Excel file helped me solve these problems:

  • Track working hours.
  • Calculate earnings.
  • Spot discrepancies in pay.
  • Manage my work calendar.
  • Enable me to track holidays.

These are similar problems as my Flickerbook web app solves, however using Excel file was not as convenient as a web app, as I would not have access to it on my mobile device back in 2010-2015, and in order to update it I would require my laptop.