# Excel - COLUMN Function Problem - Expert Solution

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
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
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
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.

Get instant expert help with Excel and Google Sheets

#### Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.