Counting salary...
Posted by Maddu on June 11, 2001 3:17 AM
Hello!
I'm trying to do this sheet that would calculate the hours worked (Cell format: Time). So I have the hours worked, for eample: from 9:00 to 19:00, 10:00 hours. Then I have the salary for each hour, for example 60, how can I count the salary, just bu multiplying the hours worked and the salary for each hour doesn't give me the right salary...
Thanks a lot in advance!
Posted by Andrew Cheung on June 11, 2001 6:29 AM
Suppose you have the begin time in [a2], end time in [b2].
In [c2], you have the formula [b2]-[a2] to get the no. of hours worked.
Then you need to format the three "TIME" cells with [hh]:mm.
In [d2], you have the hourly rate, and in [e2] you need to have the formula =[c2]*[d2]*24 and you will get the right wages.
Posted by Eric on June 11, 2001 7:29 AM
=hour(b2-a2) to return the integer of hours worked
Hi,
The problem I had using the b2-a2 routine on time-formatted data was that it gave me an actual time (eg. 19:00 - 9:00 = 10:00) which is not 10 hours, it's 10:00 O'Clock!, which is nonsensical for hourly salary calculations. Instead use =hour(b2-a2) in a "general" format column (c) to give you the actual hours between the two times. Then the salary calculation looks fine.
Posted by maddu on June 12, 2001 5:33 AM
Re: =hour(b2-a2) to return the integer of hours worked
Thanks, I tried it, but it didn't count half hours, f.ex. 9:00 19:30.
maddu
Posted by maddu on June 12, 2001 5:33 AM
Hello!
Thanks, now it works!
maddu
Posted by maddu on June 12, 2001 5:49 AM
New problem...
So, now that I get the correct salary, I should calculate it so, that after 18:00 the salary is double... So when I put the IF statement, I should state it so, that IF f.ex.
A1= starting time 09:00
A2= ending time 19:00
A3= Salary/h = 60
A4= Salary = IF(A2>18;((A2-18)*(A3*2)*24)+((18-A1)*24);A2*A8*24)
So it doesn't understand the time 18, not when it's put 18, or 18:00.
What could I do?
maddu
Posted by Eric on June 12, 2001 8:04 AM
try =HOUR(B1-A1)+(MINUTE(B1-A1)/60) in general format
Sorry for the thoughtless answer, try
=HOUR(B1-A1)+(MINUTE(B1-A1)/60) instead, that should give it to you down to the minute. Make sure the format for the result column is "general". Thanks, I tried it, but it didn't count half hours, f.ex. 9:00 19:30. maddu : Hi, : The problem I had using the b2-a2 routine on time-formatted data was that it gave me an actual time (eg. 19:00 - 9:00 = 10:00) which is not 10 hours, it's 10:00 O'Clock!, which is nonsensical for hourly salary calculations. Instead use =hour(b2-a2) in a "general" format column (c) to give you the actual hours between the two times. Then the salary calculation looks fine.
Posted by Aladin Akyurek on June 12, 2001 8:36 AM
Re: New problem...
What should be the result if the values in A1 thru A3 hold and the condition of the IF is true?
Aladin
====== : Hello! So, now that I get the correct salary, I should calculate it so, that after 18:00 the salary is double... So when I put the IF statement, I should state it so, that IF f.ex. A1= starting time 09:00 A2= ending time 19:00 A3= Salary/h = 60 A4= Salary = IF(A2>18;((A2-18)*(A3*2)*24)+((18-A1)*24);A2*A8*24) So it doesn't understand the time 18, not when it's put 18, or 18:00. What could I do?
Posted by maddu on June 13, 2001 4:33 AM
Re: New problem...
Hi,
the result should be 660 (normal salary for 9 first hours and then double salary for the last hour (after 18 o'clock)).
maddu
What should be the result if the values in A1 thru A3 hold and the condition of the IF is true? Aladin ======
Posted by Aladin Akyurek on June 13, 2001 9:43 AM
Re: New problem...
What about using
=IF(A2>"18:00"+0,((SUM(HOUR(A2),MINUTE(A2)/60)-SUM(HOUR("18:00"),MINUTE("18:00")/60))*2+(SUM(HOUR("18:00"),MINUTE("18:00")/60)-SUM(HOUR(A1),MINUTE(A1)/60)))*A3,"your else-part")
You can even put 18:00 in some cell, say in B1, and replace all "18:00" with B1. I left the else-part untouched. You can rewrite in a fashion similar to the then-part.
Aladin
=========== Hi, the result should be 660 (normal salary for 9 first hours and then double salary for the last hour (after 18 o'clock)). maddu What should be the result if the values in A1 thru A3 hold and the condition of the IF is true? : Aladin : ======
