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
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!
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
oldchippy
-------------
If you are happy with the help you have received, please click the blue scales icon in the blue bar of the post.
Click here >>> Top Excel links for beginners to Experts
Forum Rules >>>Please don't forget to read these
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 ?
Change the format to Custom and format to [hh]:mm:ss, this will add times that total over 24 hours
oldchippy
-------------
If you are happy with the help you have received, please click the blue scales icon in the blue bar of the post.
Click here >>> Top Excel links for beginners to Experts
Forum Rules >>>Please don't forget to read these
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 ???
Yes, see attached
oldchippy
-------------
If you are happy with the help you have received, please click the blue scales icon in the blue bar of the post.
Click here >>> Top Excel links for beginners to Experts
Forum Rules >>>Please don't forget to read these
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!
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
oldchippy
-------------
If you are happy with the help you have received, please click the blue scales icon in the blue bar of the post.
Click here >>> Top Excel links for beginners to Experts
Forum Rules >>>Please don't forget to read these
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!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks