Excel: Calculate wage with the rate per hour

• Tweet
• Email

• Tweet
• Email

Sometimes very simple looking problem can really tease you and make you realize what you’ve learned! Here is a simple problem Satish Jung sent me on Facebook page (http://facebook.com/ictTrends).

There is a sheet given with arrival time, departure time. Now all you have to do is calculate how many hours each person worked and what would be the wage for given rate/hour. Look at the sheet below:

It appears fairly straightforward, isn’t it? Subtract arrival time from departure time to compute working hours and multiple working hour by the given rate to compute wage. But, is it as straight as that?

When you apply = D2 – C2 on E2 cell you’ll get 9:00, 3:30 and 7:00 for Ram, Gita and Hari respectively. Everything appears as normal. Nothing to worry!

Now calculate wage. Because we are given the rate of Rs. 400 per hour for the working hours up to 8 and Rs 800 for additional hours above 8 hours. So, apply the following formula on F2:

= IF (E2 <= 8, E2 * 400, (8 * 400) + (E2 – * 800

What? It returned you 150? It’s not the right answer, no way!

So, where is the problem? Everything looks fine but the result is not correct?

Time and Time Value

Excel uses the serial date-time to store and calculate the date and time. When you enter 6:00 in a cell, excel computes the time value (i.e. 0.25), enters it into the sheet and formats the number in time format (h:mm). So, you see 6:00 but in fact the value 0.25 is entered in the cell. This 0.25 is called the time value of 6 o’clock.

You can see the time value if you format the cell into general or number format.

Excel stores dates and times as a number representing the number of days since 1900-Jan-0, plus a fractional portion of a 24 hour day:   ddddd.tttttt. The integer portion of the number, ddddd, represents the number of days since 1900-Jan-0 and the integer portion of the number, tttttt, represents the fractional portion of a 24 hour day.  For example, 6:00 AM is stored as 0.25, or 25% of a 24 hour day.  Similarly, 6PM is stored at 0.75,  or 75% percent of a 24 hour day. 

As you can see, any date and time can be stored as the sum of the date and the time.  For example, 3PM on 29-Jan-2000 is stored internally as 36544.625.  When you enter a time without a value, such as entering 15:00 into a cell, the date portion is a zero.  The zero indicates that there is no date associated with the time.  You should remember that entering just a time does not automatically put in the current date.

Where was the problem?

So, now, I hope you’ve guessed it right. Though we had 9:00 (nine hours) in Working Hour column, it is not 9 but in fact it is 0.375. And when we multiplied the working hour with 400 and 800 rupees, the computed wage was different than the expected one.

Solution?

We need to derive the number 9 in place of time 9:00. As we already learned that the time value is stored in fraction of 24 hours (1 day), we can multiply the hour in time by 24 to get the number.

0.357 x 24 = 9

Thus, let’s change the formula we used to calculate Working Hour (E2) as below:

= ( D2 – C2 ) * 24

Very odd, it returns 0:00 but don’t scared. Just format the cell into number format (Format >>Cells >> Number >> General)

Great! You got the number of working hours and the same formula on F2 (Wage) returns the correct value. Just fill the formula down to rows below to get the sheet like below:

Please configure Regional Settings to use Rs. as currency symbol and apply Currency Format for F2:F4 to show Wage with Rs. symbol.

I hope I made it clearer to Satish. Wish you best of luck and happy learning!

BTW, if you have learned properly, can you solve the similar question below? Try yourself and tell me you could do it!

Tagged As: calculate wage, excel, rate per hour, subtract time, time and time value