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

weaholt

New Member
Joined
Oct 9, 2006
Messages
18
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
Joined
Oct 9, 2006
Messages
18
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!!
 

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
Joined
Oct 30, 2014
Messages
4,243
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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
Joined
Oct 9, 2006
Messages
18
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
Joined
Oct 9, 2006
Messages
18
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
Joined
Oct 9, 2006
Messages
18

ADVERTISEMENT

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
Joined
Oct 30, 2014
Messages
4,243
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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)
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top