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 ?

 

[AlanY]

how about convert hr and min to one cell in hr first, i.e. =V7+V8/60 etc

 

[weaholt]

how about convert hr and min to one cell in hr first, i.e. =V7+V8/60 etc

You are fabulous!!! Thank you xx

 

[AlanY]

You are fabulous!!! Thank you xx

you're welcome

 

[weaholt]

I’m back again

still the same scenario as above but I need to get it to work for negative numbers. So one cell could have -2(which is hours) and the other cell 10 (which is minutes). I can’t get the above suggestion to work with negative values.

 

[AlanY]

would you show an example that the below wouldn't work

Book1
	U	V	W	X	Y
6	hrs	mins	hrs/mins
7	10	15	10.25
8	-2	10	-1.83
9			8.42	or	8 hrs 25mins
Sheet1

Cell Formulas
Range	Formula
W7:W8	W7	=U7+V7/60
W9	W9	=SUM(W7:W8)
Y9	Y9	=INT(W9)& " hrs " &(W9-INT(W9))*60&"mins"

 

[weaholt]

would you show an example that the below wouldn't work

Book1
	U	V	W	X	Y
6	hrs	mins	hrs/mins
7	10	15	10.25
8	-2	10	-1.83
9			8.42	or	8 hrs 25mins
Sheet1

Cell Formulas
Range	Formula
W7:W8	W7	=U7+V7/60
W9	W9	=SUM(W7:W8)
Y9	Y9	=INT(W9)& " hrs " &(W9-INT(W9))*60&"mins"

Thank you for helping me - I'm part of the way there now but in your example it is adding the minutes together and not deducting them...

the sum would be 10:15 added to -2:10 which should come out at 8:05 not 8:25

Any ideas?

 

[AlanY]

ok, may be this

Book1
	U	V	W	X	Y
6	hrs	mins	hrs/mins
7	10	15	10.25
8	-2	10	-2.17
9			8.08	or	8 hrs 5 mins
Sheet1

Cell Formulas
Range	Formula
W7	W7	=U7+V7/60
W8	W8	=U8+SIGN(U8)*V8/60
W9	W9	=SUM(W7:W8)
Y9	Y9	=INT(SUM(W7:W8))&" hrs "&INT((W9-INT(W9))*60)&" mins"

 

[weaholt]

ok, may be this

Book1
	U	V	W	X	Y
6	hrs	mins	hrs/mins
7	10	15	10.25
8	-2	10	-2.17
9			8.08	or	8 hrs 5 mins
Sheet1

Cell Formulas
Range	Formula
W7	W7	=U7+V7/60
W8	W8	=U8+SIGN(U8)*V8/60
W9	W9	=SUM(W7:W8)
Y9	Y9	=INT(SUM(W7:W8))&" hrs "&INT((W9-INT(W9))*60)&" mins"

Wow - how do you work it out??! That's brilliant again!! Thank you so much I really appreciate you taking the time to help me xx

 

[AlanY]

glad that help