Microsoft Excel – Timesheet Formatting

I have used timesheet during my course of work and each time, the basic information is the same. However, it irked me that they tend to require manual inputting of the dates and other information. Thus, I set out to see how I can change the way it was being done. I have yet to see someone create a less tedious way to update the timesheet when it is usually in the format show in the screenshot below.

What I have done is mostly formatted with the standard entry without any public holidays involved yet. October is mostly a working month. Since I can’t possibly remember all the formula, I had to use Google to search for the formula that matched the purpose that I had in mind.

The first was to complete the day column. Now, I didn’t want to change it every month, so, I referenced the first day of the month to the month that I had input above. After that, the rest of it is just using the addition method.

=A3+1

As I prefer to view my time in the 12-hour format, I just used formatting cells for the display. The next part was fun in formatting the lunch hour and the hours worked. They needed to be formatted using “[h];@ because I didn’t want to show the minutes yet. However, if there is a need, the formula can be changed to the following.

[h] "h" mm "min";@

Now, I wanted to calculate the hours worked excluding the lunch hour, I had used another cell to input the lunch hour and reference to it since the unpaid hour is usually 1 hour lunch. Then, I used the following formula to display only the lunch hour on weekdays, this is under the assumption that this timesheet is used for the standard working weekdays.

=IF(OR(WEEKDAY(A4)={1,7}), "", $H$3)

Now, in the “Remarks” column, I wanted to show that it was a weekend, so I used the same formula again.

=IF(OR(WEEKDAY(A3)={1,7}), A3, "")

To understand the use of 1 and 7, I had to refer to an excellent explanation here. One might think that this is easy, let’s use the same formula on conditional formatting to highlight the row that is showing a weekend. Nope.

=WEEKDAY($A3,2)>5

The reason that I used a “$” only for the column is that I want this formula to check all the columns since my dates are all in the columns. I choose to use the grey to highlight the row in the event that printouts are necessary and printing in monochrome is cheaper.

Now, my favourite part is using the inputs in the Time In and Time Out columns only and calculating the hours worked. I didn’t want to show “#VALUE” or “#NA” when there is no inputs yet, so I used the following to rectify the calculations.

=IF(OR(WEEKDAY(A4)={1,7}),"",IF(OR(ISBLANK(B4), ISBLANK(C4), ISBLANK(C4), ISBLANK(D4)),"", C4-B4-D4))

So, here I have a timesheet that works as the basic function. I have to think about using another sheet to track the annual leaves, medical leaves and other types of leave entitlement and display in the Remarks column based on the date.