SUM and ROUNDUP formulae combination

[asdfzxcvm]

Hello. I would appreciate if someone could help me to combine the SUM function and the round function such that every time I have a sum of 0.6,to round it up to 1. So, for example if I have 1.2 + 1.4, I don't want it to give me 2.6,but 3. Thank you!

 

[Joe4]

Welcome to the Board!

Something like this:

=ROUND(SUM(A1:A10),0)

 

[asdfzxcvm]

this works more or less. the thing is that even if I want to throw an exception that every time it will reach x.6,it will round it up to 1.
for example I have 0.2+1.3+1.3=3.2
3.2+1.2+0.1 = 4.5
4.1+0.1​=5

 

[asdfzxcvm]

without "even if"

 

[Joe4]

for example I have
0.2+1.3+1.3=3.2
3.2+1.2+0.1 = 4.5
4.1+0.1​=5

I do not understand the logic behind the examples.
Can you explain each one, in detail?

 

[asdfzxcvm]

I want each time the sum of the numbers after the comma is equal with 6 to be automatically rounded up with 1. so each time it reaches 0.6 = 1 but I thought I could make some kind of formula which would allow me to do this for every situation. I don't want the number to be higher than 0.6 to be equal to 1.
it's like the minutes from an hour. so for example if
1=10 minutes
2=20 minutes
...
6=60 minutes = 1h
in this case 1=0.6
hence if I add some hours with their minutes,I would have the following (3h 20min + 1h 20min +10min)
3.2+1.2+0.1 = 4.5 => which would be a total of 4h and 50min
in the second example I would have 20min + 1h 30 min + 1h 30min
0.2+1.3+1.3=3.2
it's the best example I have for this situation
I hope you understand what I am saying

 

[Joe4]

The issue is that you need to add each value, one at a time, and evaluate that. You cannot add them all up at once, and then apply rounding.
Look at this example:
1.2+1.2+1.2+1.2+1.2
If you add those up, you get 6.0. There is rounding to be applied, but that is not the correct answer for you. You are expecting 6.4.

So, what you are working with is data whose data whose format does not really represent what it is. I cannot think of any easy solution around this. If it were up to me, I would do one of two things:
1. Fix the data, so the entries are actually times and not numbers. If this is the way the data comes over and you have no control over that, I would create a macro to either fix it as they are entered or all at once.
2. Create my own Function in VBA that does special adding, where it goes through each entry, one at a time, and make the proper adjustments.

We can help you do either of those two things.

 

[asdfzxcvm]

I will set the data to show me the hours with minutes. but now I have another problem.
I have the Start and End hours with working hour being 6h. How do I compute the overtime?
below are a few examples.

 

[Joe4]

I cannot see your image from my current location.

If you want to calculate overtime, just subtract 6 hours. Since date/time subtraction in Excel uses days (1 = 1 day), hours are just a fraction of one day. So to subtract 6 hours, you would just subtract 6/24.

So, if you had some total (let's say in cell A10), and wanted to figure out all hours in excess of 6 hours, you could use a formula like this:

=MAX(0,A10-(6/24))

 

[asdfzxcvm]

alright. I'll try to do it like this and see how it goes. thank you