I want to calculate the overtime allowance as follows
A B
Col A: Working from (In Hrs:Min format and don't want seconds)
Col B: Working from (In Hrs:Min format and don't want seconds)
Col C: Diff between Col A and Col B (In Hrs:Min format and don't want seconds)
Col D: Extra 10 minutes per hour from 10:00 pm upto 6:00 am i.e. if overtime
done from 18:00 to 9:30 (i.e. 6:00pm to 9:30 am next day) he will get extra
10 min/hr (thus he gets max. 1hr and 20 Min.)
Col E: Total hrs i.e. Col C + Col D
Col F: Free hrs. As a standard rule, 1 hrs is deducted from his total hrs.
Col G: Lunch hrs i.e. If he works between 24:00 hrs to 9:30 hrs (next day)
he will not get anything else he will get 30 Min. as Lunch / Dinner hrs free
which is deducted from his total hrs.
Col H: Net hrs. (Col E - Col F - Col G)
Col I : Net hrs rounded to the nearest 30 Mins. (i.e. if it is 2:10, it
should be 2:30)
Col J: net hrs should be converted to decimal i.e. if net hrs is 8:30, it
should be convered to 8.50 so that I can calculate the amount to be paid.





--
Knowldege is Power

Look he

http://www.cpearson.com/excel/overtime.htm

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Ajit Munj" wrote in message ...
|I want to calculate the overtime allowance as follows
| A B
| Col A: Working from (In Hrs:Min format and don't want seconds)
| Col B: Working from (In Hrs:Min format and don't want seconds)
| Col C: Diff between Col A and Col B (In Hrs:Min format and don't want seconds)
| Col D: Extra 10 minutes per hour from 10:00 pm upto 6:00 am i.e. if overtime
| done from 18:00 to 9:30 (i.e. 6:00pm to 9:30 am next day) he will get extra
| 10 min/hr (thus he gets max. 1hr and 20 Min.)
| Col E: Total hrs i.e. Col C + Col D
| Col F: Free hrs. As a standard rule, 1 hrs is deducted from his total hrs.
| Col G: Lunch hrs i.e. If he works between 24:00 hrs to 9:30 hrs (next day)
| he will not get anything else he will get 30 Min. as Lunch / Dinner hrs free
| which is deducted from his total hrs.
| Col H: Net hrs. (Col E - Col F - Col G)
| Col I : Net hrs rounded to the nearest 30 Mins. (i.e. if it is 2:10, it
| should be 2:30)
| Col J: net hrs should be converted to decimal i.e. if net hrs is 8:30, it
| should be convered to 8.50 so that I can calculate the amount to be paid.
|
|
|
|
|
| --
| Knowldege is Power