How to show hours:minutes when hours in one cell and mins in another

[weaholt]

Hope someone can help. This is driving me mad.

I have a spreadsheet where staff enter the number of hours leave allowance they have. The number of hours go in cell U7 and the number of minutes on cell V7. There are also 2 cells which are completed to show whether the member of staff had any leave leftover from the previous year, the hours go in cell U8 and the minutes in cell V8.

I used U7&”:”&V7 to combine the hours and minutes to combine and then added U8&”:”&V8 to get a starting total. This worked fine when the member of staff had leave left over, but if they had a negative figure (they had used too much from the year before) when I add the results from the two formulas above I get a #value error.
I would really appreciate some help ?

 

[weaholt]

glad that help

I'm so sorry to be bothering you again!

The solution above doesn't seem to cover all times....for instance, when dealing with a negative time, so using the example above, 10:15 + -2:10 to result is correct as Y9 calcs to 8:05. And it works for lots of scenarios, but when I get to 10:15 + -2:05 it calculates to 8:09 instead of 8:10. It does the same for 10:15 + -2:07 where the result is showing as 8:07 and not 8:08. It seems to continue with this pattern, miscalculating every other number by 1 minute.

When performing the calculation in Y9 using a positive time, if the sum was to be 10:15 + 1:00 it would calculate correctly with a result of 11:15. However, if the sum changes to 10:15 + 1:01 the result stays at 11:15. If the sum was to be 10:15 + 1:02 the result is correct and stays correct all the way up to 10:15 + 1:16 where it's wrong again.

I'm sorry to be a pain!!

 

[AlanY]

it's a rounding off error, this should fix it

Book1
	T	U	V	W
6		hrs	mins	hrs/mins
7		10	15	10.25
8		-2	5	-2.08333
9				8:10
Sheet1

Cell Formulas
Range	Formula
W7:W8	W7	=U7+SIGN(U7)*V7/60
W9	W9	=INT(W7+W8)&":"&ROUND((W7+W8-INT(W7+W8))*60,0)

 

[weaholt]

it's a rounding off error, this should fix it

Book1
	T	U	V	W
6		hrs	mins	hrs/mins
7		10	15	10.25
8		-2	5	-2.08333
9				8:10
Sheet1

Cell Formulas
Range	Formula
W7:W8	W7	=U7+SIGN(U7)*V7/60
W9	W9	=INT(W7+W8)&":"&ROUND((W7+W8-INT(W7+W8))*60,0)

Thank you. I’m off work until Monday so I will follow your advice when I’m back in. I really do appreciate you helping me ?

 

[weaholt]

it's a rounding off error, this should fix it

Book1
	T	U	V	W
6		hrs	mins	hrs/mins
7		10	15	10.25
8		-2	5	-2.08333
9				8:10
Sheet1

Cell Formulas
Range	Formula
W7:W8	W7	=U7+SIGN(U7)*V7/60
W9	W9	=INT(W7+W8)&":"&ROUND((W7+W8-INT(W7+W8))*60,0)

HI

Your solution worked perfectly! Thank you so much for helping me

How do you get to be so good with excel formulas? I am self taught so aren't that great - think I need to buy some books!!

Thank you again x

 

[weaholt]

it's a rounding off error, this should fix it

Book1
	T	U	V	W
6		hrs	mins	hrs/mins
7		10	15	10.25
8		-2	5	-2.08333
9				8:10
Sheet1

Cell Formulas
Range	Formula
W7:W8	W7	=U7+SIGN(U7)*V7/60
W9	W9	=INT(W7+W8)&":"&ROUND((W7+W8-INT(W7+W8))*60,0)

Hi Alan

Thanks again for you help - it has been working brilliantly. I have come across one issue though.

When entering a zero in U8 and then a value in V8, it doesn't come up with the correct hours/mins. For instance, if it was just 40 mins, the result in W8 is showing as zero, when it should be greater than zero. DO you know how to fix this ?

 

[AlanY]

ok, that should do it

Book1
	U	V	W
6	hrs	mins	hrs/mins
7	10	15	10.25
8		40	0.67
9			10:55
Sheet1

Cell Formulas
Range	Formula
W7:W8	W7	=U7+IF(U7<0,-1,1)*V7/60
W9	W9	=INT(W7+W8)&":"&ROUND((W7+W8-INT(W7+W8))*60,0)

 

[weaholt]

Thank you ...again!!