software

So I am polling an external source that gives the average talk time for a
support agent in seconds. While this is interesting we need to see it in
minutes. I tried using condition formation, and I tried formating the cell.
I tried used [mm]:ss but it gives me astronomical numbers. Any ideas,
suggestions or tips? Here is a sample of the table:

2/5/2006 - 2/11/2006 Average of WaitTime67.94
Average of AgentTalkTime239.25
2/12/2006 - 2/18/2006 Average of WaitTime71.80
Average of AgentTalkTime318.63Sean,

I did a little searching of the newsgroup.

You could use a calculated field in the pivot table (you are using a pivot
table right?)

The formula in the calculated field should be quot;[Time Field]/24/60/60quot;
Then format this field as mm:ss

Does that help?

quot;Sean McCloskeyquot; wrote:

gt; So I am polling an external source that gives the average talk time for a
gt; support agent in seconds. While this is interesting we need to see it in
gt; minutes. I tried using condition formation, and I tried formating the cell.
gt; I tried used [mm]:ss but it gives me astronomical numbers. Any ideas,
gt; suggestions or tips? Here is a sample of the table:
gt;
gt; 2/5/2006 - 2/11/2006 Average of WaitTime67.94
gt; Average of AgentTalkTime239.25
gt; 2/12/2006 - 2/18/2006 Average of WaitTime71.80
gt; Average of AgentTalkTime318.63
gt;

It does help, but not much. The calculated field gives me an new field that
is summed. The quot;Totalquot; field it the calculated field of the average of call
times, so not a field at all.

quot;Dominicquot; wrote:

gt; Sean,
gt;
gt; I did a little searching of the newsgroup.
gt;
gt; You could use a calculated field in the pivot table (you are using a pivot
gt; table right?)
gt;
gt; The formula in the calculated field should be quot;[Time Field]/24/60/60quot;
gt; Then format this field as mm:ss
gt;
gt; Does that help?
gt;
gt; quot;Sean McCloskeyquot; wrote:
gt;
gt; gt; So I am polling an external source that gives the average talk time for a
gt; gt; support agent in seconds. While this is interesting we need to see it in
gt; gt; minutes. I tried using condition formation, and I tried formating the cell.
gt; gt; I tried used [mm]:ss but it gives me astronomical numbers. Any ideas,
gt; gt; suggestions or tips? Here is a sample of the table:
gt; gt;
gt; gt; 2/5/2006 - 2/11/2006 Average of WaitTime67.94
gt; gt; Average of AgentTalkTime239.25
gt; gt; 2/12/2006 - 2/18/2006 Average of WaitTime71.80
gt; gt; Average of AgentTalkTime318.63
gt; gt;

Ah, I see.

How are you polling the external data source?

Perhaps you could perform the calculation ( /24/60/60 ) before it gets to
excel, which would enable you to use your current pivot table structure.

quot;Sean McCloskeyquot; wrote:

gt; It does help, but not much. The calculated field gives me an new field that
gt; is summed. The quot;Totalquot; field it the calculated field of the average of call
gt; times, so not a field at all.
gt;
gt; quot;Dominicquot; wrote:
gt;
gt; gt; Sean,
gt; gt;
gt; gt; I did a little searching of the newsgroup.
gt; gt;
gt; gt; You could use a calculated field in the pivot table (you are using a pivot
gt; gt; table right?)
gt; gt;
gt; gt; The formula in the calculated field should be quot;[Time Field]/24/60/60quot;
gt; gt; Then format this field as mm:ss
gt; gt;
gt; gt; Does that help?
gt; gt;
gt; gt; quot;Sean McCloskeyquot; wrote:
gt; gt;
gt; gt; gt; So I am polling an external source that gives the average talk time for a
gt; gt; gt; support agent in seconds. While this is interesting we need to see it in
gt; gt; gt; minutes. I tried using condition formation, and I tried formating the cell.
gt; gt; gt; I tried used [mm]:ss but it gives me astronomical numbers. Any ideas,
gt; gt; gt; suggestions or tips? Here is a sample of the table:
gt; gt; gt;
gt; gt; gt; 2/5/2006 - 2/11/2006 Average of WaitTime67.94
gt; gt; gt; Average of AgentTalkTime239.25
gt; gt; gt; 2/12/2006 - 2/18/2006 Average of WaitTime71.80
gt; gt; gt; Average of AgentTalkTime318.63
gt; gt; gt;

I'm pulling it from an SQL DB.

quot;Dominicquot; wrote:

gt; Ah, I see.
gt;
gt; How are you polling the external data source?
gt;
gt; Perhaps you could perform the calculation ( /24/60/60 ) before it gets to
gt; excel, which would enable you to use your current pivot table structure.
gt;
gt;
gt;
gt;
gt; quot;Sean McCloskeyquot; wrote:
gt;
gt; gt; It does help, but not much. The calculated field gives me an new field that
gt; gt; is summed. The quot;Totalquot; field it the calculated field of the average of call
gt; gt; times, so not a field at all.
gt; gt;
gt; gt; quot;Dominicquot; wrote:
gt; gt;
gt; gt; gt; Sean,
gt; gt; gt;
gt; gt; gt; I did a little searching of the newsgroup.
gt; gt; gt;
gt; gt; gt; You could use a calculated field in the pivot table (you are using a pivot
gt; gt; gt; table right?)
gt; gt; gt;
gt; gt; gt; The formula in the calculated field should be quot;[Time Field]/24/60/60quot;
gt; gt; gt; Then format this field as mm:ss
gt; gt; gt;
gt; gt; gt; Does that help?
gt; gt; gt;
gt; gt; gt; quot;Sean McCloskeyquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; So I am polling an external source that gives the average talk time for a
gt; gt; gt; gt; support agent in seconds. While this is interesting we need to see it in
gt; gt; gt; gt; minutes. I tried using condition formation, and I tried formating the cell.
gt; gt; gt; gt; I tried used [mm]:ss but it gives me astronomical numbers. Any ideas,
gt; gt; gt; gt; suggestions or tips? Here is a sample of the table:
gt; gt; gt; gt;
gt; gt; gt; gt; 2/5/2006 - 2/11/2006 Average of WaitTime67.94
gt; gt; gt; gt; Average of AgentTalkTime239.25
gt; gt; gt; gt; 2/12/2006 - 2/18/2006 Average of WaitTime71.80
gt; gt; gt; gt; Average of AgentTalkTime318.63
gt; gt; gt; gt;

Good. You should definitely be able to perform that calc within the query and
return it ready to go to excel.

Let me know if you need a hand.

Are you using MS query?

quot;Sean McCloskeyquot; wrote:

gt; I'm pulling it from an SQL DB.
gt;
gt; quot;Dominicquot; wrote:
gt;
gt; gt; Ah, I see.
gt; gt;
gt; gt; How are you polling the external data source?
gt; gt;
gt; gt; Perhaps you could perform the calculation ( /24/60/60 ) before it gets to
gt; gt; excel, which would enable you to use your current pivot table structure.
gt; gt;
gt; gt;
gt; gt;
gt; gt;
gt; gt; quot;Sean McCloskeyquot; wrote:
gt; gt;
gt; gt; gt; It does help, but not much. The calculated field gives me an new field that
gt; gt; gt; is summed. The quot;Totalquot; field it the calculated field of the average of call
gt; gt; gt; times, so not a field at all.
gt; gt; gt;
gt; gt; gt; quot;Dominicquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; Sean,
gt; gt; gt; gt;
gt; gt; gt; gt; I did a little searching of the newsgroup.
gt; gt; gt; gt;
gt; gt; gt; gt; You could use a calculated field in the pivot table (you are using a pivot
gt; gt; gt; gt; table right?)
gt; gt; gt; gt;
gt; gt; gt; gt; The formula in the calculated field should be quot;[Time Field]/24/60/60quot;
gt; gt; gt; gt; Then format this field as mm:ss
gt; gt; gt; gt;
gt; gt; gt; gt; Does that help?
gt; gt; gt; gt;
gt; gt; gt; gt; quot;Sean McCloskeyquot; wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; gt; So I am polling an external source that gives the average talk time for a
gt; gt; gt; gt; gt; support agent in seconds. While this is interesting we need to see it in
gt; gt; gt; gt; gt; minutes. I tried using condition formation, and I tried formating the cell.
gt; gt; gt; gt; gt; I tried used [mm]:ss but it gives me astronomical numbers. Any ideas,
gt; gt; gt; gt; gt; suggestions or tips? Here is a sample of the table:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; 2/5/2006 - 2/11/2006 Average of WaitTime67.94
gt; gt; gt; gt; gt; Average of AgentTalkTime239.25
gt; gt; gt; gt; gt; 2/12/2006 - 2/18/2006 Average of WaitTime71.80
gt; gt; gt; gt; gt; Average of AgentTalkTime318.63
gt; gt; gt; gt; gt;

I am using what ever Excel uses by default, so I would assume yes.

quot;Dominicquot; wrote:

gt; Good. You should definitely be able to perform that calc within the query and
gt; return it ready to go to excel.
gt;
gt; Let me know if you need a hand.
gt;
gt; Are you using MS query?
gt;
gt;
gt;
gt;
gt; quot;Sean McCloskeyquot; wrote:
gt;
gt; gt; I'm pulling it from an SQL DB.
gt; gt;
gt; gt; quot;Dominicquot; wrote:
gt; gt;
gt; gt; gt; Ah, I see.
gt; gt; gt;
gt; gt; gt; How are you polling the external data source?
gt; gt; gt;
gt; gt; gt; Perhaps you could perform the calculation ( /24/60/60 ) before it gets to
gt; gt; gt; excel, which would enable you to use your current pivot table structure.
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;Sean McCloskeyquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; It does help, but not much. The calculated field gives me an new field that
gt; gt; gt; gt; is summed. The quot;Totalquot; field it the calculated field of the average of call
gt; gt; gt; gt; times, so not a field at all.
gt; gt; gt; gt;
gt; gt; gt; gt; quot;Dominicquot; wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; gt; Sean,
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; I did a little searching of the newsgroup.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; You could use a calculated field in the pivot table (you are using a pivot
gt; gt; gt; gt; gt; table right?)
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; The formula in the calculated field should be quot;[Time Field]/24/60/60quot;
gt; gt; gt; gt; gt; Then format this field as mm:ss
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Does that help?
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; quot;Sean McCloskeyquot; wrote:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; So I am polling an external source that gives the average talk time for a
gt; gt; gt; gt; gt; gt; support agent in seconds. While this is interesting we need to see it in
gt; gt; gt; gt; gt; gt; minutes. I tried using condition formation, and I tried formating the cell.
gt; gt; gt; gt; gt; gt; I tried used [mm]:ss but it gives me astronomical numbers. Any ideas,
gt; gt; gt; gt; gt; gt; suggestions or tips? Here is a sample of the table:
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; 2/5/2006 - 2/11/2006 Average of WaitTime67.94
gt; gt; gt; gt; gt; gt; Average of AgentTalkTime239.25
gt; gt; gt; gt; gt; gt; 2/12/2006 - 2/18/2006 Average of WaitTime71.80
gt; gt; gt; gt; gt; gt; Average of AgentTalkTime318.63
gt; gt; gt; gt; gt; gt;

Ok, let's try it this way first.

Are you polling the external data to a worksheet first, then creating the
pivot table from that worksheet?

Or are you creating the pivot table directly from the data source? That is,
when you created the pivot table, did you choose the quot;external data sourcequot;
option?

If the former, then the solution is easy, you can just add a helper column
to the spreadsheet that divides the seconds amound by 24 / 60 / 60.

If the latter, then click the pivot table wizard, click back, the click get
data. This should bring up the query the pivot is using.

Click a new column in the 'data pane' (the bottom one), and type [seconds
field name] /24/60/60

Obviously substitute the correct field name for [seconds field name]. This
should give you a new field to use in the pivot. Substitute this field for
the one you are currently using for the seconds.

How does that work?
quot;Sean McCloskeyquot; wrote:

gt; I am using what ever Excel uses by default, so I would assume yes.
gt;
gt; quot;Dominicquot; wrote:
gt;
gt; gt; Good. You should definitely be able to perform that calc within the query and
gt; gt; return it ready to go to excel.
gt; gt;
gt; gt; Let me know if you need a hand.
gt; gt;
gt; gt; Are you using MS query?
gt; gt;
gt; gt;
gt; gt;
gt; gt;
gt; gt; quot;Sean McCloskeyquot; wrote:
gt; gt;
gt; gt; gt; I'm pulling it from an SQL DB.
gt; gt; gt;
gt; gt; gt; quot;Dominicquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; Ah, I see.
gt; gt; gt; gt;
gt; gt; gt; gt; How are you polling the external data source?
gt; gt; gt; gt;
gt; gt; gt; gt; Perhaps you could perform the calculation ( /24/60/60 ) before it gets to
gt; gt; gt; gt; excel, which would enable you to use your current pivot table structure.
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; quot;Sean McCloskeyquot; wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; gt; It does help, but not much. The calculated field gives me an new field that
gt; gt; gt; gt; gt; is summed. The quot;Totalquot; field it the calculated field of the average of call
gt; gt; gt; gt; gt; times, so not a field at all.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; quot;Dominicquot; wrote:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Sean,
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; I did a little searching of the newsgroup.
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; You could use a calculated field in the pivot table (you are using a pivot
gt; gt; gt; gt; gt; gt; table right?)
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; The formula in the calculated field should be quot;[Time Field]/24/60/60quot;
gt; gt; gt; gt; gt; gt; Then format this field as mm:ss
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Does that help?
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; quot;Sean McCloskeyquot; wrote:
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; So I am polling an external source that gives the average talk time for a
gt; gt; gt; gt; gt; gt; gt; support agent in seconds. While this is interesting we need to see it in
gt; gt; gt; gt; gt; gt; gt; minutes. I tried using condition formation, and I tried formating the cell.
gt; gt; gt; gt; gt; gt; gt; I tried used [mm]:ss but it gives me astronomical numbers. Any ideas,
gt; gt; gt; gt; gt; gt; gt; suggestions or tips? Here is a sample of the table:
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; 2/5/2006 - 2/11/2006 Average of WaitTime67.94
gt; gt; gt; gt; gt; gt; gt; Average of AgentTalkTime239.25
gt; gt; gt; gt; gt; gt; gt; 2/12/2006 - 2/18/2006 Average of WaitTime71.80
gt; gt; gt; gt; gt; gt; gt; Average of AgentTalkTime318.63
gt; gt; gt; gt; gt; gt; gt;