Brainsofsteel header

How to Create a Time Sheet in Excel

Subject: tutorials

Description: How to create a simple timesheet for time recording using an excel spreadsheet

Posted by David Caldwell on 17/11/15 at 18:48

There are times when we have to record hours worked and calculate if we have met our contracted hours or if we are in credit or deficit.

Adding hours and minutes being base sixty can be difficult for the human mind to do but luckily we can let Microsoft Excel take the strain.

Step One

Create a grid six by five as below.  Populate the first row with the days of the week and the first column with time in and time out repeated twice.  Is there anyone who gets a paid lunch break?

Step Two

Select the cell range as below and left click your mouse.  Choose ‘Format Cells’, ‘Custom’ then ‘hh:mm’.

Step Three

Type in the formula =SUM(B4-B3)+(B6-B5) in cell B:7.  The formula represents the time out values minus the  time in values for both morning and afternoon.

Copy this formula accross so that it populates cells B:7 to F:7.

Step Four

Click in cell G:7 and drag your mouse to B:7 choose the auto sum icon and G:7 will display the totals of B:7 to F:7. 

Important Bit!

To make this a true total of time worked you will need to format cell G:7 as Custom ‘[h]:mm:ss’ and not ‘hh:mm’.

Step Five

Prettify your spreadsheet and enter your time recording values making sure to put a zero in front of the hour if a single digit and a colon ‘:’ between the Hours and minutes.

Happy Time Recording!