I've this data

A B C

1 Date Device Duration

2 Mon, 3/28/2011 office connection 1 hour, 2 minutes, 43 seconds

3 Mon, 3/28/2011 office connection 27 seconds

4 Tue, 3/29/2011 house connection 2 minutes, 1 second

5 Mon, 3/28/2011 shop connection 4 minutes, 0 seconds

. . . .

.

.

100The question is: i want another sheet to sum the duration of each connection for each day. Sample output calculated in the other sheet would be:

A B C

1 Date Device Total duration

2 Mon, 3/28/2011 office connection 1 hour, 2 minutes, 70 seconds

3 Mon, 3/28/2011 shop connection 4 minutes, 0 seconds

4 Tue, 3/29/2011 house connection 2 minutes, 1 secondand so on.

I've tried to use SUMIFS(C2:C100, B2:B100, =office connection, A2:A100, =Mon, 3/28/2011) but the value returned is zero. Is there any way to get this done

If that's what your data looks like, then I doubt you will ever get it to work. Post an example of what is actually in your spreadsheet.

Read this how-to to learn how to post your data in this forum:

http://www.computing.net/howtos/sho...

MIKE

A B C 1 Date Device Duration 2 Mon, 3/28/2011 office 1 hour, 2 minutes, 43 seconds 3 Mon, 3/28/2011 office 27 seconds 4 Tue, 3/29/2011 house 2 minutes, 1 second 5 Mon, 3/28/2011 shop 4 minutes, 0 seconds . . . 100

Now i would like to sum the durations for each device in another sheet so that my output would look like thisA B C 1 Date Device Total duration 2 Mon, 3/28/2011 office 1 hour, 2 minutes, 70 seconds 3 Mon, 3/28/2011 shop 4 minutes, 0 seconds 4 Tue, 3/29/2011 house 2 minutes, 1 second

I have tried using the SUMIFS(range, criteria range, criteria, criteria range, criteria) but the value returned is 0. Is there any way to get this done? I hope my presentation is better now.

Your Time column ( column C ) is going to give you all kinds of headaches. The usual Format for a time cell is

HH:MM:SS = Hours:MINUTES:SECONDS = 01:02:43How are you entering the data into column C?

Is it by hand or do you have a formula?

What does the formula look like?

Are you Custom Formatting your TIME cells?Also, are you using a Custom Format for you DATE column?

MIKE

I get the data from a monitoring software in that format and then need to do a summarry which involves calculating the total duration so yes the data is gotten somewhat manually since i just copy and paste it

If your getting your data from another source, and just doing a copy/paste, then all your data is in TEXT format. You will need to convert your DATE column to real Dates,

and your TIME column to real Times.First,

To convert your DATE column try this:

Open a new column NEXT to column A, so you now have a new empty column B

In cell B2 enter the formula:=DATEVALUE(RIGHT(A2,9))

and drag down as many rows as needed.

You should see the same date in Column B as there is in column A

Now, in Column B, select your whole range of new Dates and

On the Task Bar

Select Copy

Select Paste Special

Select Values

Click OKThe Dates in Column B are now real dates and not just the result of a formula.

See how that works.

MIKE

ok i get the dates in the format Mon, 3/28/2011 becomes 3/28/2011.

What next?Thanks for the first step.

OK, but first here is a modifed formula for the Date's, =DATEVALUE(RIGHT(A2,10))

The old one will give you an error message if the date is 10 characters long,

so a date of 3/28/2011 which is nine charters long will be OK,

but a date of 12/12/2011 won't work.When I have a fix for the Time values will post it.

MIKE

I wasn't able to come up with one formula for all

the possible conditions in your Time column.So, here are two formulas that should do the job.

If a cell has Hours, Minutes and Seconds

or a cell has Minutes and Seconds

use this formula:=IF(COUNTIFS(D2,"*hour*"),SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D2,"seconds",""),"minutes,",":"),"hour,",":"),"0:"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D2,"seconds",""),"minutes,",":"),"hour,",":"))

(It is a very long formula, copy and paste it)

If a cell has

onlySeconds

use this formula:="00:00:"&LEFT(D3,FIND(" ",D3,1))

Use two blank columns, lets say column E & FIn column E enter the two above formulas to get

just the Hours, Minutes and Seconds into a format

that Excel will understand.In column F enter the formula =TIMEVALUE(),

it works the same as the =DATEVALUE() formula.Then once you have converted all your times do a Copy / Paste Special / Values.

See how that works.

MIKE

If, after you do the =TIMEVALUE(D2) section and you see: 0.043553241

0.0003125

0.043553241

0.01875

0.001400463That is how Excel actually stores the Time, just format the column as HH:MM:SS

MIKE

Thanks for the reply. It works quite well but for the cases where my cell contains say data that isn't plural say "1 hour" , "1 minute" or '1 second' or when the data is say "1 hour, 23 minutes" with no seconds involved, in such cases i need to add ", 0 seconds" to get the correct format. is there any way around this? Apart from that I'm getting quite good results. Thanks alot.

Also, is it possible to do some OR kind of statement so it checks for hour or hours, minute or minutes and second or seconds.

The possible combination of different formats makes it extremely difficult to use this data effectively and trying to write one formula or even two or three to fit all possibilities is daunting. With just the three words

Hour, Minute, and Secondyou can have about 7 different combo's:Hour Minute Second

Hour Minute

Hour Second

Minute Second

Second

Hour

Minuteadd in plurals to one or more words........and you can see how it just grows.

You should try to talk to who ever is in charge of the monitoring software and see if they can modify the output to be more Excel friendly.

Or you may be better of pre-processing the date before importing it into Excel.

There is probably a VBA solution to your problem, but unfortunately my VBA skills are nill.

Perhaps someone will jump in with a VBA solution.

MIKE

Thanks, I see it is more complicated than I thought.

I'll make do with what I have so far and hope there's someone with a VBA solution (whatever that is) like you said.

Visual Basic for Applications (VBA),

it's a programing language used inside Excel,

you can do a lot of wonderful and magical things with it.When you create a Macro your using VBA.

MIKE

Ask Your Question

Weekly Poll