You can see the finished calculator below.

We are going to make a shortcut and build the wage calculator on top of my time card calculator. So let's start by downloading the time card calculator (hosted by mediafire).

Open the time card calculator in Excel and make the following changes: change the title to "Wage calculator". Type in "hourly wage", "overtime wage", "minimum time to pay", "regular working time rounded" and "rounded overtime".

Everything else is pretty much self explanatory, but the minimum time to pay may need a little explanation.

The minimum time to pay means the lowest time period that is to be charged, which is a pretty common practice. I have chosen 15 minutes for the sake of this tutorial.

This means we are going to

**round up the working time into 15 minute intervals**. So, if the actual regular working time is 53 hours and 8 minutes, we will round it up to 53 hours and 15 minutes.

Let's try this. Change the "Check out" time for monday 2.1.2012 from 17:30 to 16:20 so we'll get a total of 53:05 for the regular working hours.

So, "regular working time rounded" and "rounded overtime" will be the actual total working time

**rounded up to nearest 15 minutes**. This can be done with the following function:

=CEILING(VALUE;1/96)Note, depending on your regional settings you might have to use comma (,) as a separator instead of semicolon (;): =CEILING(VALUE,1/96)

So the regular working hours (total time is 53:05) rounded up to nearest 15 minutes would be:

=CEILING(G18;1/96)You will get a result of 2,21875... Change the cell's formatting into hours and minutes. Right click in the cell, choose format cells and from the opening dialogue choose custom. Click to the "type" field and type in [h]:mm. Click OK and you'll have a result of 53:15.

**Note: we use the [h]:mm instead of h:mm because the latter will start counting from zero each time the value goes over 24 hours.**

Repeat this for the overtime too (Total of 3:54) and you should have exactly 4 hours.

Next type in the "wage for regular hours", "overtime wage" and "total" as seen in the image below. You can style the cells in any way you want from

**home tab > styles > cell styles**.

Next we just count the wages for regular working time (hourly wage multiplied with the rounded up working time):

Note: we multiply the result with 24 because one hour for Excel is 1/24:

=B7*B4*24and same thing for overtime (overtime wage multiplied with the rounded overtime):

=B8*B5*24And finally sum these up and you should have the following results:

And we are done. Here is the final result:

Hello sir, How are you today?

ReplyDeleteIt helps me to know about this. You have been shown importance topic in your content. It will inspire me always. This is very nice post! I will bookmark this blog as I have Colorado Springs Mortgage Company focuses primarily on Colorado springs mortgage designed to meet your needs. Providing Colorado Springs and the greater El Paso nation area. Ft. Carson, Peterson and Schriever effective responsibility and veterans– we are here to provide you!

Thank You Very Much For a Nice & Cool Article.

Hi, I tried to download the Wage Calculator XLS from mediafire but it's invalid. Can someone give me the downloadable link? Thanks.

ReplyDelete