Keeping Track of Working Hours with Excel

So, I have to fill up this time sheet at work. However, my handwriting is illegible no matter how so, I decided to duplicate the current form with an Excel spreadsheet embedded in the Word document. Took me a few months to work out the kinks since I don’t really have time to work on it consecutively.

Calculate Overtime

My current time sheet is now more or less done to fulfill this current situation. Obviously I needed a start time and end time. I also need to calculate the total hours I have spent in the office, excluding lunch time. That part is easy. The tricky part was to calculate the overtime hours. So, I searched for the formula and used the following:

=IF(E2=””,””, IF(E2>Overtime!$A$2, E2-Overtime!$A$2,””))

Now, Overtime is the name of the sheet and I used the following in the cell:

8:30

The cell has been formatted to display the above while using “Time” as the format. The hours above is actually the minimum number of hours that I need to work.

I used the If function twice because I want to display the calculation only if E2 where the total number of hours worked does not contain “”. Excel has a weird way of detecting null and blanks, so I used “” for the cell E2 if no start time, end time or lunch time has been entered.

Remarks for Public Holidays and Leaves

I wanted to have the public holidays and leaves in the “Remarks” section. So, took quite a few days for this to work since I am not familiar with how Match works. I find this part tricky. Thus, I created 2 columns, one for the public holiday and the other for the leaves. The third column is to take input from either column.

The following is to check the spreadsheet Public Holiday for any dates that is a public holiday, of course.

=IF(ISNA(MATCH(A2,’Public Holiday’!$B$2:$B$12,0)),””,INDEX(‘Public Holiday’!A$2:A$12,MATCH(A2,’Public Holiday’!$B$2:$B$12,0)))

The following is to check the spreadsheet Leave Type for any leaves applied.

=IF(ISNA(MATCH(A2,’Leave Type’!$B$2:$B$12,0)),””,INDEX(‘Leave Type’!A$2:A$12,MATCH(A2,’Leave Type’!$B$2:$B$12,0)))

The following is to check the cells if there is anything in them.

=IF(H2=””, I2, H2)

Here’s how my time sheet looks like before I hide the columns for “Public Holiday” and “Leave Type”.

For those who may not know, the $ sign is to tell Excel that the cells won’t change when I copy and paste the formulae for the subsequent cells.

To understand the following formula, I’ve linked them to the websites that I found that helped me understand during the time when I implemented them.

Index – Go to Tech on the Net.

Match – Go to Able Bits.

IsNA – Got to Excel Jet.

I hope this helps someone who may want to use or implement a time sheet for small office use. There are other methods to keep track of the working hours using databases for larger number of employees.