Data Choices

We compile. You decide.

CONVERT Military to Civilian or vice versa by clicking here!

click the calculator!

or scroll to the bottom of the page!

Timesheets in Excel - Calculations based on time in Microsoft Excel

(the form below uses military time for easy entry but displays the time civilian style automatically!)

A lot of times people need to conduct calculations based upon time. Excel makes this easy, but sometimes it looks complicated. Let's try to clear things up a bit here! First lets look at what we wish to be the final product:

(If this is not what your were looking for tell us by clicking here!)

Start your worksheet by typing MONDAY in cell B2. Then move the cursor to the lower right of cell B2 until it becomes a little crosshair. Then drag and drop over to F2. Note that the days of the week automatically drop into place.

You can manually input Bob and Mary's names and the clock in, clock out references. In cell B4, for Bob's clock in time, enter 7:00. The cell shows 7:00 and correctly refers to 7AM. But for clock out, when you enter 3:00, it shows 3:00, but references 3:00AM, not 3:00PM!

An easy way to change this is to use military time. Now 3:00PM is actually 15:00, and you will note that if you enter 15:00 into cell B5, 15:00 appears, but the formula bar shows 3:00PM, which is indeed the time we want. However, we may not want to see the value in military time, even though we enter it that way to make data entry easy.

With BOTH B4 and B5 highlighted, right mouse click and select FORMAT CELLS. Select TIME, and then 1:30 PM and then click OK. Now you get 7:00AM and 3:00PM as shown above.

Again with both cells highlighted, right mouse click and select COPY, and then highlight C4:F5 and right mouse click and select PASTE SPECIAL and FORMATS. Copy those same formats into the appropriate cells for Mary. Now all those cells will accept a military time entry, but reflect a civilian time with AM and PM clearly posted.

There are only three things left to do: Create the formulas to total the time for each employee for the week based on the clock in and clock out values, have the total column automatically notify you if an employee exceeds forty hours for the week (an overtime situation) and color code the table in a manner that lets you easily distinguish between employees and days of the week.

In cell G4 enter

=(B5-B4)+(C5-C4)+(D5-D4)+(E5-E4)+(F5-F4)

The parenthesis are not really necessary for Excel, because addition and subtraction have the same precedence factor. However they do help us humans easily see what groupings we have applied to the timesheet formula.

With G4 as the active cell you should right mouse click, select FORMAT CELLS and then select time and the 37:30:55 time format.

You can then copy and paste to G7, which will not only copy the formatting, but also copy the relative cell references, and you should be able to verify that the formula now in cell G7 is

=(B8-B7)+(C8-C7)+(D8-D7)+(E8-E7)+(F8-F7)

Now for the overtime notification. With G7 as the active cell, go into FORMAT and CONDITIONAL FORMATTING from the pull down menu bar at the top of the Excel Worksheet. Set condition one as cell value is GREATER THAN and enter 40:00:00 directly into the box. Then click the FORMAT button and select a red bold italic text and click on OK and then ADD. Copy and PASTE SPECIAL the format back to cell G4.

Now note that when you enter the clock in and clock out times, if the total of those time differences exceeds forty hours for the week, the text appears in red, as follows:

Of course you could choose a second conditional formatting to appear at perhaps 33:00:00 hours, so you would easily see when an employee is approaching an overtime situation and may obtain overtime if he works another full day. By leaving the above mentioned formatting in place, you would still see the values jump to bold red italic once overtime occurred.

Formatting the worksheet to let you easily distinguish between employees and days of the week is left up to you to pick the colors that work best for you.

Excel and Loan Amortization SERP Challenge Video! Real Estate Blog Keyword Effectiveness and DCI

Conversion Calculator!

Report a problem with the converter or make a suggestion

If you would like to be emailed anytime this report is updated simply enter your email address below and click the SUBMIT button. We may also send other articles and items that may interest you in the near future!