Note: this tutorial makes use of many Excel's functions and data formatting tools, so it might take some effort to understand. But that is what it takes to learn the way Excel handles time. You will also be happy about the new tools you can apply in your work outside the scope of this tutorial.

### Finished calculator

### Tutorial

1. Let's lay out a skeleton of our table first. Insert the following data to a new Excel sheet. As you can see, nothing fancy yet, just column titles and some extra bits we need.

You can put your own regular working hours to cell B3, i put 8 hours for the sake of this tutorial.

**Note: make sure the regular working hours you type are in number format, not time.**

2. Let's put down our time period to cells B2 and B3. For the sake of this tutorial, type 1.1.2012 to cell B2. If you want, you can define the ending date to cell B3, but i have decided to take a short cut and make Excel fetch me the last day of the month automatically. Don't worry, i am going to explain how to do it, see image below.

You can get Excel to find you the last day of the month with the formula you see marked with red:

DATE(YEAR(1.1.2012);MONTH(1.1.2012)+1;0)

**Note: depending on your regional settings, you might have to use commas (,) instead of semicolons (;) as a separator in the function. I have used semicolons.**

So in our example, we type:

DATE(YEAR(B2);MONTH(B2)+1;0)Explanation:

DATE function requires three parameters:

**DATE(YEAR;MONTH;DAY)**. First one is pretty self explanatory in our function, but the month and the day need to be explained.

Well, we have to add one month to the date (MONTH(B2)+1) so that january becomes february. We then use zero for the last parameter - day, which forces Excel to find the last day of the previous month.

Not that intuitive at first glance, is it? But hey, only few of us understand how the remote control actually works (i don't) and still we know how to use it.

So now we have our time period. Let's continue.

3. Let's add the dates when we have worked. Insert the following dates to the first column.

Make sure they are in date format after you've typed them. To check, click any cell that has a date and check what does it say in

**Home tab**>

**Number group**(see image below).

Next we will make Excel return the day of the week based on the dates we inserted. Insert the following function to cell B6.

WEEKDAY function will give us a number representing the day of the week (monday = 1, tuesday = 2, etc.). Next let's drag copy the function down the column. Grab from the little black box in bottom right corner of the cell and drag down. Then we will turn the numbers into text.

Let's turn the numbers in to names of week now. Select all the cells from the column and go to home tab, and click the drop down menu from the number group. From the menu select the last option: more number formats.

In the opening dialogue, select the last option "custom" and type DDDD to the field shown in the image shown below. Hit enter. This will give you the full name of the week. DDD will give the abbreviation.

Presto! Now we have the names of the week.

4. Let's count the regular working hours next, so let's put down the times we have checked in to work and out. Remember to put down the lunch times too. You can use any set of times you wish, i have chosen bunch of random times for this tutorial (see image below).

Now we can count our regular working time. Because we want to separate the overtime hours from regular hours, we will use the IF function (if you are not familiar with IF function, read my IF function tutorial first):

- First we test if the working time minus lunch time is greater than 8 hours (the regular working hours we put in cell B3).
- If it is, we shall display a number 8 in the cell.
- If it is less than 8, we will display the real working hours.

The function:

=IF(((D6-C6)+(E6-F6))*24>B3;B3;((D6-C6)+(E6-F6))*24)

**Note: depending on your regional settings, you might have to use commas (,) instead of semicolons (;) as a separator in the function. I have used semicolons.**

**Explanation:**

**Logical test:**((D6-C6)+(E6-F6)) gives us the working hours when we take out the lunch time. We have to multiply the result with 24 because for Excel 1 hour is 1/24. We then compare the result to the regular working hours we typed to cell B3 (in red): ((D6-C6)+(E6-F6))*24**>B3.****Value if true**:**we print out cell B3.****Value if false**: ((D6-C6)+(E6-F6))*24) gives us the real working hours after we take out the lunch time. Again we have to multiply the result with 24 because for Excel 1 hour is 1/24.

You can do this by editing the function: click in G6 function and press F2 on your keyboard. Then move with arrow keys to the first B3 and press F4 from your keyboard. This will add dollar signs: $B$3, which means the cell is completely locked. Do the same again to the next B3 (see image below).

5. Next we will count the overtime with the IF function. Type this function to cell H6. Notice that i have already locked the cell B3 in all instances in the function (B3 -> $B$3).

=IF(((D6-C6)+(E6-F6))*24>$B$3;(((D6-C6)+(E6-F6))*24-$B$3);0)

**Note: depending on your regional settings, you might have to use commas (,) instead of semicolons (;) as a separator in the function. I have used semicolons.**

**Explanation:**

**Logical test:**((D6-C6)+(E6-F6)) gives us the working hours when we take out the lunch time. We have to multiply the result with 24 because for Excel 1 hour is 1/24. We then compare the result to the regular working hours we typed to cell B3 (in red): ((D6-C6)+(E6-F6))*24**>$B$3. So this part is the same as in the regular working time function.****Value if true**:**we calculate the whole working time (((D6-C6)+(E6-F6))*24 and take out the 8 hours of regular working time (in red): (((D6-C6)+(E6-F6))*24****-$B$3**)**Value if false**: zero.

You can do this by editing the function: click in H6 function and press F2 on your keyboard. Then move with arrow keys to the first B3 and press F4 from your keyboard. This will add dollar signs: $B$3, which means the cell is completely locked. Do the same again to the next B3.

This is what we have so far:

Let's not worry about the working time and overtime being in decimal format at the moment, let's transform them in to time in the end.

One last thing before converting the decimal numbers to time is to count totals. So click to cell G13 and click autosum from

**Home tab > Editing > Autosum**(see image below).

This will give you grand total of 50,90. Do the same for overtime, you should get total of 3,9.

6. Let's transform the decimal values in to time, as in hours and minutes. This is a bit more complicated procedure than you would expect, but let's take one step at a time.

**In order to change a numeral value into time, we have to divide the value with 24 and then change the data to custom: h:mm.**

So let's edit the function in cell G6, click on the cell and press F2 from your keyboard to edit it. Let's make following changes (in red):

=IF(((D6-C6)+(E6-F6))*24>$B$3;$B$3Explanation:/24;((D6-C6)+(E6-F6)))~~*24~~

- Value if true: we have to divide the 8 hours regular working time with 24 in order to format it as time.
- Value if false: we have remove the *24 part from the end for the same reason.

=IF(((D6-C6)+(E6-F6))*24>$B$3;$B$3/24;((D6-C6)+(E6-F6)))Drag copy the function down the column.

For the overtime, let's edit the function in cell H6, click on the cell and press F2 from your keyboard to edit it. Let's make following changes (in red):

=IF(((D6-C6)+(E6-F6))*24>$B$3;(((D6-C6)+(E6-F6))*24-$B$3)Explanation: we have to divide the "value if true" value with 24 before we can format it as time./24;0)

Now we can change the values into time. Select all the cells in regular working yours and overtime and go to Home tab > number group > select more number formats from the drop down menu (see images below).

**With the regular time format we chose Excel cannot show time that exceeds 24 hours. It goes back to counting from zero every time it hits 24.**

This is easy to fix though, just go to the same Format cells dialogue again (

**home tab > number > drop down > more number formats**, see the images above).

Choose

**custom**from category and select

**[h]:mm:ss**.

Now you will see 54:15:00 in regular hours and 3:54:00 in overtime hours.

7. Finally, let's do some formatting to make our table prettier. Select your styles from

**home tab > styles group > Cell styles**. I am going to choose heading 3 style for my heading and total style for my totals row (see image below).

So here we have the finished calculator:

That's it for now, if you have any questions or suggestion, let me know in the comment section below. Cheers!

### Free Download Time Card Calculator Excel

Download link for finished time card calculator here (hosted by mediafire).

This is fantastic, save one thing... What if I have employees that come in say at 10pm and work until 6am.... The downloaded spreadsheet had a heart-attack when I pluged it in that way....

ReplyDeleteI don't know about all of you, but somehow I've memorized my 50+ caseload and all their goals. That's approximately 250 goals....no wonder I can't remember where I put my keys half the time. :) time tracker software

ReplyDelete