Adding Hours:Minutes

**winnie_shrub** · 09-18-2009, 04:42 AM

Dear Clever People,

I have always had problems with time values in Excel and today is no exception....

Unsurprisingly I have a spreadsheet:
Column D contains the total hours that a component has been installed for
Column C contains the additional hours the component acquired

I need the total of these 2 sets of time added together to give me the new hours that the component has acquired. All cells have been formatted [h]:mm:ss

I attach an extract of the spreadsheet. At the moment the values in column D are all calculated manually. When I add the values together Excel returns #Value!

The annoying thing is that in cells B2 and A3 I tested this and can do this with no problem...the value in B3 is the sum of B2 and A3. What am I doing wrong?

**daddylonglegs** · 09-18-2009, 04:57 AM

Excel doesn't recognise time values of 10,000 hours or greater when entered directly in a cell, so when you put 28777:24 in D6 this is treated as a text string.

You can use a workaround because excel quite happily returns totals higher than 10,000 hours as long as the inputs are lower.

Using that approach try putting this formula in D6

="9777:24"+"9500:00"+"9500:00"

That adds up to the correct number of hours and is a valid time value....so now your =D6+C7 formulas should work.....(you might have to change formatting/cell size so that they display properly....., i.e. format cells as [h]:mm)