I'm learning Excel and I find it very exciting because it can challenging. However, I sometimes do have difficulties like in the following exercise. I need to calculate the daily allowance of an employee. Please note that the names and titles are in German because I'm in Switzerland. I use Excel 2003.
Here is my question: If an employee stays for more than 12 hours, he will get the full daily allowance, otherwise he will only get half. The day starts at 0:00 (midnight). So if an employee arrives at 11:00, he will receive the full daily allowance - as he will stay there for 13 hours. If he arrives at 13:00, he will only get half because he stays for less than 12 hours on that day.
Please check with my Excel file, rows 5,6 and 7. I have used the same employee but the arrivals and departures are different. I have used the same employee because he gets the same daily allowance and it is easy to compare. The daily allowance will be in column J. I used the following method to calculate the allowance in column J, which proofed to be wrong:
H5 = Duration = G5 - F5
J5 = Allowance = =TRUNC(H5)*42+(MOD(H5;1)>0
I would appreciate it very much if someone could help me solve this problem, by finding the correct formula in J5.
Thanks a lot