Excel 2007 : something like concatenate with if criteria ? or sthing else?

[svmatso]

Hi there,
I have an exctracted report with time values in format hh:mm:ss in 8 columns and thousands of rows.
Whereas i can make calculations with these data, some values are considered as zeros. Thes are
the time values that represent time less than an hour and are in a format :mm:ss instead of hh:mm:ss.
When I sum up values in column where the values are of the format :mm:ss i get zeros.
If I use the concatenate formula i can add a "0" value and that would solve the problem.
But what about columns where these values are mixed?
Here is a sample. please notice that in the totals row the first 2 columns return me zeros instead of the real sum.
Avg ACD Time Avg ACW Time Staffed Time ACD Time ACW Time AUX Time Avail Time Other
:02:38 :00:07 31:05:39 21:43:35 1:35:11 3:28:56 3:18:33 :42:15
:02:22 :00:14 31:47:09 18:54:49 2:53:22 4:18:54 4:11:19 1:12:11
:02:21 :00:27 31:28:12 18:59:26 4:13:35 4:07:50 2:41:20 :54:26
:02:08 :00:13 31:57:07 17:35:48 2:17:25 4:19:14 6:06:20 1:23:26
:02:20 :00:06 32:07:11 18:33:44 1:11:51 2:53:23 8:02:29 1:09:36
:02:10 :00:09 30:37:49 16:01:15 2:53:21 4:28:54 4:54:59 2:01:51
TOTALS 0:00:00 0:00:00 189:03:07 111:48:37 15:04:45 23:37:11 29:15:00 5:47:04

So i wonder if there could be a formula that would check the format and then concatenate the values.
If the value were in format hh:mm:ss would return the value.
If the value were in format :mm:ss would concatenate ("0";A1)

any ideas..?
please find attached a sample of the report

thnx in advance...

[abousetta]

Hello and welcome to the forum,

You can use a simple If statement to check and amend according.

In another cell you would put this formula:

Please Login or Register  to view this content.

Then drag down and to the right to recreate your table.
Add headers, formatting and the sum formula and you are ready to go.

Alternately you can just copy/ paste values over your original table.

Have a look at the attachment.

abousetta

[oeldere]

When you import the data you have to give them the right value hh:mm:ss

Now the data in the fist 2 collumn are text (for Excel).

Then you can't count with the data.