» Adding Text Values Representing Time
CATEGORY - Excel Date & Time Formulas
VERSION - All Microsoft Excel Versions
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
Book Store:
Recommended Books:
- The Complete Book of Business Plans: Simple Steps to Writing a Powerful Business Plan (Small Business Sourcebooks)
- Microsoft Windows XP Step by Step (With CD-ROM)
- The Fall of Advertising and the Rise of PR
- Microsoft Excel 2002 Visual Basic for Applications Step by Step
- Special Edition Using Microsoft Excel 2002
- Wall Street Journal Guide to Understanding Money and Investing
oldchippy
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
10x
emvipici
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
Change the format to Custom and format to [hh]:mm:ss, this will add times that total over 24 hours
emvipici
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 ??? :confused:
oldchippy
Yes, see attached
emvipici
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
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
[url]http://www.cpearson.com/excel/datearith.htm#AddingTimes[/url]
emvipici
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!
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.:eek:
can anyone help me ?
10x!