Time card calculator Excel tutorial - download free Excel spreadsheet

This is a Microsoft Excel guide on how to create a time card calculator in Excel 2007 and 2010. Follow the tutorial and learn to create and modify a calculator on your own, or skip to the end and download the finished Excel spreadsheet. Let's get started.



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


Time card calculator Excel tutorial - download free Excel spreadsheet


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.

Time card calculator skeleton

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.

Time card calculator Excel tutorial - get the last day of month

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.


Time card calculator Excel tutorial - insert dates

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).

Time card calculator Excel tutorial - check dates
 Next we will make Excel return the day of the week based on the dates we inserted. Insert the following function to cell B6.

Time card calculator Excel tutorial - get the week day by date


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.

Time card calculator Excel tutorial - drag copy

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.

Time card calculator Excel tutorial - change formatting

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.

Time card calculator Excel tutorial - custom formatting

Presto! Now we have the names of the week.

Time card calculator Excel tutorial - weekdays

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).

Time card calculator Excel tutorial - times


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):
  1. First we test if the working time minus lunch time is greater than 8 hours (the regular working hours we put in cell B3).
  2. If it is, we shall display a number 8 in the cell.
  3. If it is less than 8, we will display the real working hours.
So let's type the IF function in to cell G6 (see the image below).

Time card calculator Excel tutorial - regular hours if function

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:
  1. 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.
  2. Value if true: we print out cell B3.
  3. 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.
Before we drag copy the function from G6 down the column, lock the B3 cells in the function (because we want Excel to use B3 in every instance of the function).

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).

Time card calculator Excel tutorial - lock cells


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:
  1. 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.
  2. 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)
  3. Value if false: zero.
Again, before we drag copy the function from H6 down the column, lock the B3 cells in the function (because we want Excel to use B3 in every instance of the function).

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:

Time card calculator Excel tutorial - numbers

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).

Time card calculator Excel tutorial - autosum
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$3/24;((D6-C6)+(E6-F6))*24)
Explanation:
  1. Value if true: we have to divide the 8 hours regular working time with 24 in order to format it as time.
  2. Value if false: we have remove the *24 part from the end for the same reason.
This is what you should have in G6:
=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)/24;0)
Explanation: we have to divide the "value if true" value with 24 before we can format it as time.

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).


Time card calculator Excel tutorial - custom

Now you will see that everything else looks good but this does not work for the totals at all. Why do we see 6:15 and 3:54?

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:

Time card calculator Excel tutorial - download free Excel spreadsheet

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).

1 comment:

  1. 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....

    ReplyDelete