Microsoft Excel
--
Questions
--
Followers
Top Experts
How to calculate daily allowance with Excel 2002
Hello everyone
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
Massimo
Â
Question-20.10.2010.xls
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
Massimo
Â
Question-20.10.2010.xls
Zero AI Policy
We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.
Try this?
=(ROUND(DAUER*2,0))/2*
IF(Tarifgruppe=1,46,
IF(Tarifgruppe=2,44,
IF(Tarifgruppe=3,42,
IF(Tarifgruppe=4,40))))
=(ROUND(DAUER*2,0))/2*
IF(Tarifgruppe=1,46,
IF(Tarifgruppe=2,44,
IF(Tarifgruppe=3,42,
IF(Tarifgruppe=4,40))))
J5 ... Â =IF(H5>=12,H5*42,H5*21)
Hard to follow some of the names but I am guessing that Duration is the hours worked? Â If so then just use the hours worked by 12 hours or more and multiply by 42 (Again guessing that is the allowance) and if it is less than 12 multiply the hours worked by 21.
Copy and paste that down the column of J5 ... J7 ... J99 whatever and it should work.
Hard to follow some of the names but I am guessing that Duration is the hours worked? Â If so then just use the hours worked by 12 hours or more and multiply by 42 (Again guessing that is the allowance) and if it is less than 12 multiply the hours worked by 21.
Copy and paste that down the column of J5 ... J7 ... J99 whatever and it should work.
ASKER CERTIFIED SOLUTION
membership
Log in or create a free account to see answer.
Signing up is free and takes 30 seconds. No credit card required.
SOLUTION
membership
Log in or create a free account to see answer.
Signing up is free and takes 30 seconds. No credit card required.
@JollyECS, good point about the half days for arrival and departure.
EARN REWARDS FOR ASKING, ANSWERING, AND MORE.
Earn free swag for participating on the platform.
I think 33962216 Author:JollyECS nailed it.
Thanks teylyn.
I liked your 'CEILING'. That one was new to me, so thanks for increasing my knowledge!
I liked your 'CEILING'. That one was new to me, so thanks for increasing my knowledge!
SOLUTION
membership
Log in or create a free account to see answer.
Signing up is free and takes 30 seconds. No credit card required.
This question has been classified as abandoned and is being closed as part of the Cleanup Program. Â See my comment at the end of the question for more details.
Get a FREE t-shirt when you ask your first question.
We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.
Microsoft Excel
--
Questions
--
Followers
Top Experts
Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.