formula with constraints
I have a spreadsheet for a work schedule.
Cell C10 - time in, eg 9:30AM
Cell D10 - time out, eg 6:30 PM
Cell E10 calculates the hours of 9
I need F10 to return a number based on the following.
If someone works more than 7 hours they need to take a one hour lunch, if they work less than 7 but more than 4 hours, they need to take a 30 minute lunch, if they work a shift that is less than 4 hours they don't get a "lunch".
So I need F10 to return to me the hours that the employee will be paid. The value of E10 less the "lunch" time, if any.
Any help is Greatly appreciated!!!
Thank you,
DevilsLvr
Hello & Welcome to the Board,
This seems a little messy, but it seems to work...
=IF(MOD(D10-C10,1)*24>7,MOD(D10-C10,1)*24-TIME(1,0,0)*24,IF(AND(MOD(D10-C10,1)*24>4,MOD(D10-C10,1)*24<7),MOD(D10-C10,1)*24-TIME(0,30,0)*24,MOD(D10-C10,1)*24))
HTH
Regards, Jeff
If you like the answer(s) provided, why not add some reputation by clicking the * below
Please use [ Code ] tags when posting [ /Code ]
Please view/read the Forum rules --- How to mark a thread as solved
Hello and welcome to the forum,
Maybe look at the attached workbook and tell me if this is what you are looking for.
abousetta
Please consider:
Thanking those who helped you. Click the star icon in the lower left part of the contributor's post and add Reputation.
Cleaning up when you're done. Mark your thread [SOLVED] if you received your answer.
Hi abousetta,
One observation...What if they work past midnight?
Example:
C10 = 9:30 AM
D10 = 6:30 AM
Result is 21 hours
So a minor update...instead of...
=(D10-C10)*24 which produces -3 with the above variables
Change to...
=MOD(D10-C10,1)*24 which produces 21 with the above variables
Just a thought. I know the OPs example does not cross midnight, but could be the next question.
HTH
Regards, Jeff
If you like the answer(s) provided, why not add some reputation by clicking the * below
Please use [ Code ] tags when posting [ /Code ]
Please view/read the Forum rules --- How to mark a thread as solved
Hi Jeff,
You are right... I sort of stayed away from this issue intentionally since the original example didn't show this as a possibility. But the formula should cover all the bases for it to be complete.
abousetta
Please consider:
Thanking those who helped you. Click the star icon in the lower left part of the contributor's post and add Reputation.
Cleaning up when you're done. Mark your thread [SOLVED] if you received your answer.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks