Question description:
This user has given permission to use the problem statement for this
blog.
I need a formula that will have a maximum limit of 8 in the first column and carry any excess to the second column.
Solved by M. E. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
04/09/2018 - 09:45
Hi, welcome to Got it Pro-Excel!
Excelchat Expert
04/09/2018 - 09:46
According to my diagnosis, you need help with conditional formula such as IF, is that correct?
User
04/09/2018 - 09:46
Yes
Excelchat Expert
04/09/2018 - 09:47
If I understood correctly, you want to input a value for example in A1 then B1 should have a limit of 8 then the excess will be in C1, can you confirm?
User
04/09/2018 - 09:48
This is for a timesheet that auto filles the time when hours are selected.
Excelchat Expert
04/09/2018 - 09:48
I see. so the excess is technically overtime.
User
04/09/2018 - 09:49
yes
Excelchat Expert
04/09/2018 - 09:49
Can you see our shared sheet?
User
04/09/2018 - 09:49
Yes
Excelchat Expert
04/09/2018 - 09:50
I'll just do a test
Excelchat Expert
04/09/2018 - 09:50
so if the Hours is 12
User
04/09/2018 - 09:50
Ok
Excelchat Expert
04/09/2018 - 09:50
You'd like regular hours to be 8
Excelchat Expert
04/09/2018 - 09:50
excess to overtime, which is 4, is that correct?
User
04/09/2018 - 09:50
Correct
Excelchat Expert
04/09/2018 - 09:50
Ok, for that we'll use the IF function.
Excelchat Expert
04/09/2018 - 09:52
To explain the IF function, this is the syntax.
Excelchat Expert
04/09/2018 - 09:52
IF(logical_expression, value_if_true, value_if_false)
Excelchat Expert
04/09/2018 - 09:53
logical_expression is a condition that needs to return if it's true or false.
Excelchat Expert
04/09/2018 - 09:54
For example, if our expression is 2 > 1
Excelchat Expert
04/09/2018 - 09:54
Then it will return as true
Excelchat Expert
04/09/2018 - 09:54
Please look at cell C4
Excelchat Expert
04/09/2018 - 09:54
A4, I meant.
Excelchat Expert
04/09/2018 - 09:54
Can you see the simple formula in there?
Excelchat Expert
04/09/2018 - 09:55
I just wanted to show you a sample of logical_expression.
Excelchat Expert
04/09/2018 - 09:55
Do you know how to use formulas, in general?
User
04/09/2018 - 09:55
Yes so what I am looking for is =IF>8 =8 But how do I carry over to cell B
Excelchat Expert
04/09/2018 - 09:56
If you'll look at cell B2 and C2, it's already setup.
Excelchat Expert
04/09/2018 - 09:56
Try changing the A2 to whatever value you like.
Excelchat Expert
04/09/2018 - 09:57
Is that the formula you're looking for?
Excelchat Expert
04/09/2018 - 09:57
Try changing it to below 8 or 8 exactly
Excelchat Expert
04/09/2018 - 09:58
Is that what you need?
User
04/09/2018 - 09:58
This looks easy but with the time already populating I'm not sure if this will work as already have a formula in the space.
Excelchat Expert
04/09/2018 - 09:59
What do you mean the time already populating?
User
04/09/2018 - 09:59
This looks to work and I thank you for your help.
Excelchat Expert
04/09/2018 - 09:59
No worries, my pleasure. Just wanted to make sure if this is the formula you're looking for.
User
04/09/2018 - 09:59
my time sheet already has a formula in it to capture the hours worked.
Excelchat Expert
04/09/2018 - 10:00
Or if there's any problem with you using this.
Excelchat Expert
04/09/2018 - 10:00
Just like what we have in cell A2?
User
04/09/2018 - 10:01
NO going to get the formula for you. It is used to grab hours worked during day.
Excelchat Expert
04/09/2018 - 10:01
Ok, kindly share it so we can customize it to your needs.
Excelchat Expert
04/09/2018 - 10:01
When our timer hits below 3-minute mark, feel free to extend our session. Absolutely free of charge.
User
04/09/2018 - 10:04
=SUM((C12-B12)+(E12-D12))*24
[Uploaded an Excel file]
Excelchat Expert
04/09/2018 - 10:04
Ok let me take a look at that.
User
04/09/2018 - 10:05
Sorry should have started with this.
Excelchat Expert
04/09/2018 - 10:05
No no, no worries.
User
04/09/2018 - 10:08
as we have multiple shift we need the times to be adjustable with am and pm. This is causing me a headache.
Excelchat Expert
04/09/2018 - 10:08
No worries, I'll get back to you in a moment.
User
04/09/2018 - 10:09
Much appreciated.
Excelchat Expert
04/09/2018 - 10:11
Can you tell me what cell the hours worked is stored in?
Excelchat Expert
04/09/2018 - 10:12
Also, what sheet to work in as there's a lot of sheet in here.
Excelchat Expert
04/09/2018 - 10:15
Hi, are you still there?
User
04/09/2018 - 10:16
Please go to any tab other than the summary sheet. Column f shows the formula
Excelchat Expert
04/09/2018 - 10:16
Ok, so you have Hours column that has a formula.
Excelchat Expert
04/09/2018 - 10:17
You want that to not have anything more than 8?
User
04/09/2018 - 10:18
Yes the hours column would be maxed at 8 and OT column to show excess hours over 8
Excelchat Expert
04/09/2018 - 10:18
Ok, so to not mess anything in your current workbook. Can I create a new sheet that you can use as template with the formula that you need?
User
04/09/2018 - 10:19
Sure
Excelchat Expert
04/09/2018 - 10:19
If you need a new cutoff sheet. Just copy the sheet in the same workbook.
User
04/09/2018 - 10:22
Sorry not understanding your last comment
Excelchat Expert
04/09/2018 - 10:22
So the template contains the adjusted formula that you need.
Excelchat Expert
04/09/2018 - 10:23
If you want to retain the formula you can just copy or duplicate the actual sheet.
Excelchat Expert
04/09/2018 - 10:23
I'll explain the formula once I send this back to you.
Excelchat Expert
04/09/2018 - 10:23
Give me a few more minutes, almost done.
Excelchat Expert
04/09/2018 - 10:25
Here's your new file.
[Uploaded an Excel file]
Excelchat Expert
04/09/2018 - 10:26
If you'll check the template sheet.
Excelchat Expert
04/09/2018 - 10:26
The only change applied was a new formula.
User
04/09/2018 - 10:28
That is awesome. Thank you. I could not figure this out.
Excelchat Expert
04/09/2018 - 10:28
This was the original formula = =SUM((C11-B11)+(E11-D11))*24
Excelchat Expert
04/09/2018 - 10:28
I still used the original formula but I included it inside an IF Function.
Excelchat Expert
04/09/2018 - 10:28
That's good to hear.
Excelchat Expert
04/09/2018 - 10:29
=IF(SUM((C11-B11)+(E11-D11))*24>=8,8,SUM((C11-B11)+(E11-D11))*24)
Excelchat Expert
04/09/2018 - 10:29
The hours worked will never go beyond 8 hours
Excelchat Expert
04/09/2018 - 10:29
And everything excess of 8 hours will be in OT.
Excelchat Expert
04/09/2018 - 10:30
In concept this is the formula I used
Excelchat Expert
04/09/2018 - 10:31
For hours worked, =IF( Hours worked >= 8 , 8 , Hours worked)
Excelchat Expert
04/09/2018 - 10:31
For OT, =IF( Hours worked > 8 , Hours worked - 8 , 0)
Excelchat Expert
04/09/2018 - 10:32
To translate it, for hours worked - If Hours worked is greater than or equal to 8 then it should be 8 otherwise just return the hours worked.
User
04/09/2018 - 10:32
I have another glitch in a formula on this timesheet. Having issues with the formula. it is how to pull hours from other pages to calculate Stat Holiday time. You can see this on the Feb 7-20th tab on the 18th.
Excelchat Expert
04/09/2018 - 10:32
For OT - If Hours worked is greater than 8 then Subtract 8 from Hours worked otherwise 0.
User
04/09/2018 - 10:32
I know I have used all my time and then some.
Excelchat Expert
04/09/2018 - 10:34
I understand, we only have 1-question policy per session but allow me to give you a little extra and bend the rules a little. I may get into trouble but I can explain it to my superior for you.
Excelchat Expert
04/09/2018 - 10:35
I'm looking at it and it's not a glitch, it's an actual formula.
Excelchat Expert
04/09/2018 - 10:36
If you don't want it you can just delete the formula or manually enter your specific value just like on other day's holiday.
User
04/09/2018 - 10:37
As you can see I have been working on this for some time. I am trying to capture the previous 4 weeks hours and dividing those hours by 5% as well as the same hours by the vacation pay rate (summary sheet). As the other hours are a guess It would be nice to have this actually calcuate the stat pay.
Excelchat Expert
04/09/2018 - 10:39
What do you mean other hours?
User
04/09/2018 - 10:40
This should work out to be something like 6.8 hours not 44.88 hours
Excelchat Expert
04/09/2018 - 10:40
I just want to let you know that we only have 5 minutes left and we could no longer extend. I'll try to work on this until our time runs out but I cannot guarantee to finish your new question in time.
User
04/09/2018 - 10:42
Other stat hours on Tabs are a guess in hours and trying to get this formula correct to complete this timesheet for 2019.
User
04/09/2018 - 10:43
I will take any suggestions you have in the time remaining. I totaly understand that I have used mega time today.
Excelchat Expert
04/09/2018 - 10:44
No worries,
Excelchat Expert
04/09/2018 - 10:44
here try to use this on your Feb7-20 sheet for the 18th.
Excelchat Expert
04/09/2018 - 10:44
=(SUM(F11:F21)+SUM(G11:G21)+SUM('Jan 24-Feb6'!F25:G25)+SUM('Jan 10-23'!F22:G24))*Summary!K21
Excelchat Expert
04/09/2018 - 10:45
That sums up all the hours that you indicated but properly adding it and multiplying it to vacation rate.
Excelchat Expert
04/09/2018 - 10:45
which is 4%
Excelchat Expert
04/09/2018 - 10:45
If that's all, I'd like to wish you have a very nice day ahead of you and we'd love to hear from you again. Thank you for using Got it Pro-Excel!
Feel free to end the session and leave a rating and comment if you liked our service. I'll do the same and rate you 5-stars as a user since your a great learner and I'd like for you to be one of our high-rated users. warm regards!
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.
