Adding Text Values Representing Time

**ExcelTip** · 08-23-2005, 12:04 PM

Problem:

The times listed in column A are formatted as text.
When trying to add them using a simple SUM formula (=SUM(A2:A5)), a false result of 0 is returned.
How can we add text values correctly?

Solution:
Use the SUM and TIMEVALUE functions as shown in the following Array formula:
{=SUM(TIMEVALUE(A2:A5))}

To apply Array formula:
Select the cell, press

**emvipici** · 11-28-2006, 07:25 AM

I tryed to solve the problem ur way...
but it doesn't work:

When I use
{=SUM(TIMEVALUE(A2:A5))}
i get the result {#VALUE!}

I tryed another way:
{=SUM(TIMEVALUE(A2):TIMEVALUE(A5))}
I can't even use it, i get an error message.

can anyone help me ?

10x!

**oldchippy** · 11-28-2006, 09:36 AM

What you need to do is enter the formula in the cell, without the braces

=SUM(TIMEVALUE(A2:A5))

and with the cursor still in the formula bar, press Ctrl + Shift + Enter, then you will see

{=SUM(TIMEVALUE(A2:A5))} in the formula bar

**emvipici** · 11-28-2006, 11:21 AM

Thanks, it worked!

Though, I encountered another problem: I can't add times if it's over 24 hours!

please check it in the image attached.

is there a solution for this ?

**oldchippy** · 11-28-2006, 11:56 AM

Change the format to Custom and format to [hh]:mm:ss, this will add times that total over 24 hours

**emvipici** · 11-28-2006, 12:06 PM

I know this procedure works to display the sum (even if it's grater than 24h), but in my case, still doesn't work!

Does it on tour computer ???

**oldchippy** · 11-28-2006, 12:34 PM

Yes, see attached

**emvipici** · 11-29-2006, 03:04 AM

Ah, yes...

but you must keep the same table formats:
time spent [format TEXT]
formula {=SUM (TIMEVALUE(C1:C3))}

In this case, is still working ?
Mine doesn't!

**oldchippy** · 11-29-2006, 05:19 AM

Hello again,

Take a look at this link about adding and subtracting time values, it explains why the value in C5 is not displaying as expected because it is over 24 hours

http://www.cpearson.com/excel/datearith.htm#AddingTimes

**emvipici** · 11-29-2006, 05:41 AM

The thing is I'm not allowed to change the cell format...
Thanks a lot anywayz!

I'll just have to relay on the fact that there's no time greater than 24h.

10x again!