Spreadsheet: 88695d1288543444-nested-if-statement-otv5-form
Sheet: Sheet1

Overtime Worksheet |  |  |  |  |  |  |  |  |  |  |  | Critical Date | 2005-01-05 00:00:00
Employee's name | [NAME] |  |  | Level |  | 5 |  |  | [NAME] claimable | 12 |  | Eligible for Meal Allowance? | yes
Employee's number | 5678 |  |  | Step |  | 1
Travel Overtime | Date | Day | Holiday | Time In | convert | Time Out | convert | Overtime hours | RAW Travel time hours | ClaimableTravel time hours | Overtime  Multiplier | Overtime Earned | eligible | dbl/sgl | Meal Allowance | ELIGIBLE? | hours eligible | Night Differential | Total Due
Yes | 2010-10-01 00:00:00 | Friday | 0 | 08:00:00 | 8 | 20:15:00 | 20.25 | 0 | 12.25 | 12 | 1.5 | 1080 | 1 | 1 | 5 | 0 | 0 | 0 | 1085
Yes | 2010-10-06 00:00:00 | Wednesday | 0 | 17:30:00 | 17.5 | 22:30:00 | 22.5 | 0 | 5.000000000000001 | 5.000000000000001 | 1.5 | 450.0000000000001 | 1 | 1 | 5 | 1 | 0.5 | 3 | 458.0000000000001
no | 2010-10-09 00:00:00 | Saturday | 0 | 08:00:00 | 8 | 19:00:00 | 19 | 11 | 0 | 0 | 1.5 | 990 | 0 | 2 | 0 | 0 | 0 | 0 | 990
no | 2010-10-10 00:00:00 | Sunday | 0 | 22:00:00 | 22 | 01:30:00 | 1.5 | 3.500000000000001 | 0 | 0 | 2 | 420.0000000000001 | 1 | 2 | 10 | 1 | 3.5 | 21 | 451.0000000000001
no | 2010-10-11 00:00:00 | Monday | 1 | 08:00:00 | 8 | 17:00:00 | 17 | 9.000000000000002 | 0 | 0 | 2 | 1080 | 0 | 2 | 0 | 0 | 0 | 0 | 1080
 |  |  |  |  |  |  |  | 23.5 | 17.25 | 17 | 8.5 | 4020 | 1 |  | 20 |  |  | 24 | 4064
 |  |  |  |  |  | Notes:
 |  |  |  |  |  | Travel overtime- |  | this pertains to time spent on travelling while rendering overtime.  Usually this happens if an employee is to partake
 |  |  |  |  |  |  |  | in a company-sponsored event within the city or out of the country
 |  |  |  |  |  |  |  | MAX number of hours to be claimed is 12 hours
 |  |  |  |  |  |  |  | this columns is the computation of number of travel time hours earned if "Yes" is selected from column A (Travel overtime)
 |  |  |  |  |  | edit JM |  | I think my formula works just fine but I do not know how to include the maximum 12-hour condition.  Because there
 |  |  |  |  |  |  |  | are instances in which an employee may have longer than 12-hour flight (from North America to Asia)
 |  |  |  |  |  |  |  | Only employees hired before 05Jan05 are entitled to receive a $5 meal allowance
Travel Time |  |  |  |  |  |  |  | if an employee worked beyond 7pm during holidays and weekends, he gets $10 ($5*2)
Yes |  |  |  |  |  | edit JM |  | If he works beyond 7pm on regular working days, he's just entitled to 1 meal allowance
No |  |  |  |  |  |  |  | Regular working days and time are Monday to Friday, 8-5pm
 |  |  |  |  |  |  |  | This is an enetilement if the overtime work is rendered from 10pm until 6 am the next day.
Travel Time Question |  |  |  |  |  | >> crucuial to the calculation! |  | The employee should have started before or exactly 10 pm for him to be eligible for this entitlement
Yes |  |  |  |  |  |  |  | This is equal to 10% of his hourly rate.
 |  |  |  |  |  | edit JM |  | I do not exactly how to factor in the in-and-out time in the formula.
 |  |  |  | IF($G7>0.916667,VLOOKUP($G$3,Sheet2!$A$14:$K$18,(Sheet1!$G$4+1)*0.1,FALSE),0)