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


submit to reddit 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!

Keywords:




comments powered by Disqus

 Subscribe in a reader

RSS Feed

Latest Posts

17/11/15
How to Create a Time Sheet in Excel
15/11/15
Handling time and dates in PHP
03/02/15
How to Enhance Photographs with the Gimp
25/01/15
How to Cook Pigeon
25/01/15
Telling the Time
24/01/15
An Old Travel Card
10/09/14
How to Learn the Nine Times Table
09/08/14
How to Calculate the Direction between two Points
01/08/14
Making a large Google XML sitemap
18/05/14
How to make a pyramid