Question description:
This user has given permission to use the problem statement for this
blog.
I have a schedule for my colleagues and I would like excel to automatically remove their break time from the allocated hours I have inputted for that week, based on the amount of hours they have set within that day. So if they're doing 4 hours, they get no break. If they're doing between 5 to 8 hours, they get 30 minutes. 9 hours or more they get 45 minutes.
Solved by S. J. in 52 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
User
22/09/2018 - 12:40
Hello?
Excelchat Expert
22/09/2018 - 12:40
Welcome to Excelchat, I see that your question is about formulas
User
22/09/2018 - 12:40
Sorry the questions I was given I cannot seem to answer
User
22/09/2018 - 12:40
So I thought it ended
User
22/09/2018 - 12:40
Yes that is correct :)
Excelchat Expert
22/09/2018 - 12:40
Before we get started, this is a reminder that our policy is 1 problem per session with additional Q&A on that problem as time allows.
Excelchat Expert
22/09/2018 - 12:41
Let me ask you a couple of quick questions to make sure I fully understand your problem.
Excelchat Expert
22/09/2018 - 12:41
Can you tell me the high-level goal you are trying to achieve?
User
22/09/2018 - 12:41
Not sure I understand the question?
User
22/09/2018 - 12:42
Oh, as in my main goal I'm trying to achieve
Excelchat Expert
22/09/2018 - 12:42
Yes
Excelchat Expert
22/09/2018 - 12:42
Do u have a sample document so I can address to your problem?
User
22/09/2018 - 12:42
Basically, I have a small little bar and I no longer pay for my staff breaks
User
22/09/2018 - 12:42
But, I don't want it to take it off what I allocate my hours for that week
User
22/09/2018 - 12:43
Yeah sure
Excelchat Expert
22/09/2018 - 12:43
Thank you, I'll wait for it
Excelchat Expert
22/09/2018 - 12:44
Based on what you've shared, you need a formula using if statement
User
22/09/2018 - 12:45
Okay
Excelchat Expert
22/09/2018 - 12:45
I understand what the question is, I’ll start working on the solution and will be updating you as I work.
User
22/09/2018 - 12:45
Okay lovely, thank you so much
Excelchat Expert
22/09/2018 - 12:45
This should take me 30mins or less.
User
22/09/2018 - 12:46
As I've copied it across I don't think the formulas would've copied correctly
Excelchat Expert
22/09/2018 - 12:47
Can you just add the file using the paper click at the right of your chat box
User
22/09/2018 - 12:47
There we go, no it's got it.
User
22/09/2018 - 12:47
The allocation is at the bottom
Excelchat Expert
22/09/2018 - 12:47
Paper clip*
User
22/09/2018 - 12:49
Two seconds
Excelchat Expert
22/09/2018 - 12:50
Use the paper clip at the right or your chat box to send the file
Excelchat Expert
22/09/2018 - 12:50
So I can fully understand your goal
User
22/09/2018 - 12:50
Yeah just removing names quickly
Excelchat Expert
22/09/2018 - 12:52
Ok, wait
Excelchat Expert
22/09/2018 - 12:53
is you c9 value equal to 0 or 8?
Excelchat Expert
22/09/2018 - 12:55
what is your initial value in target replen?
Excelchat Expert
22/09/2018 - 12:56
Hello
Excelchat Expert
22/09/2018 - 12:56
Are you still there?
Excelchat Expert
22/09/2018 - 01:23
Since you lack in information, this is the best thing that I can only do.
[Uploaded an Excel file]
Excelchat Expert
22/09/2018 - 01:25
The formula states that if the value in S (Cell C4) is equal to or lesser than 4 it will give a value which is 0.
Excelchat Expert
22/09/2018 - 01:27
While if the value in S (Cell C4) is equal to or lesser than 8 it will give a value which is 30.
Excelchat Expert
22/09/2018 - 01:28
Else, it will give a value which is 45.
Excelchat Expert
22/09/2018 - 01:28
That goes the same with the other cells.
Excelchat Expert
22/09/2018 - 01:29
I also give additional table if you are using a number of hours.
Excelchat Expert
22/09/2018 - 01:31
Thanks for coming to Excelchat. Feel free to leave any comments or feedback. Have a nice day.
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.