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

#### weaholt

##### New Member
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

##### New Member

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

### Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

#### AlanY

##### Well-known Member
it's a rounding off error, this should fix it

Book1
TUVW
6hrsminshrs/mins
7101510.25
8-25-2.08333
98:10
Sheet1
Cell Formulas
RangeFormula
W7:W8W7=U7+SIGN(U7)*V7/60
W9W9=INT(W7+W8)&":"&ROUND((W7+W8-INT(W7+W8))*60,0)

Last edited:

#### weaholt

##### New Member
it's a rounding off error, this should fix it

Book1
TUVW
6hrsminshrs/mins
7101510.25
8-25-2.08333
98:10
Sheet1
Cell Formulas
RangeFormula
W7:W8W7=U7+SIGN(U7)*V7/60
W9W9=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

##### New Member
it's a rounding off error, this should fix it

Book1
TUVW
6hrsminshrs/mins
7101510.25
8-25-2.08333
98:10
Sheet1
Cell Formulas
RangeFormula
W7:W8W7=U7+SIGN(U7)*V7/60
W9W9=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

##### New Member

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

Book1
TUVW
6hrsminshrs/mins
7101510.25
8-25-2.08333
98:10
Sheet1
Cell Formulas
RangeFormula
W7:W8W7=U7+SIGN(U7)*V7/60
W9W9=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

##### Well-known Member
ok, that should do it

Book1
UVW
6hrsminshrs/mins
7101510.25
8400.67
910:55
Sheet1
Cell Formulas
RangeFormula
W7:W8W7=U7+IF(U7<0,-1,1)*V7/60
W9W9=INT(W7+W8)&":"&ROUND((W7+W8-INT(W7+W8))*60,0)

#### weaholt

##### New Member
Thank you ...again!!

Replies
0
Views
201
Replies
3
Views
291
Replies
8
Views
214
Replies
1
Views
67
Replies
2
Views
112

1,141,203
Messages
5,704,929
Members
421,372
Latest member
Jamie11

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back