#### Pivot Table Report: Getting average of difference of two date columns

```Hello

I have data in excel sheet as:

WORK_ID	PROJECT	START_DT	END_DT
1001	                P1	1/1/2006	               1/10/2006
1002	                P1	1/25/2006	2/20/2006
1003	                P1	3/15/2006	3/20/2006

I want to have a report that will give me average time taken to finish a work in a project.  That is, I have to take average of (end_dt - start_dt) for a project group. How do I do this in a pivot table report. When I use a calculate field to get the days worked for a work_id using DATEDIF(START_DT, END_DT, "D") then excel computes only sum on that (pivot table report always uses sum on calculated field). Other solution is to add a column in my source data to compute the difference of start and end dates and then I can use average. I would like to see this computation by using Pivot table report and the source data only in the above format. Report should look as:

PROJECT           AVERAGE TIEM TO FINISH A WORK
P1                      13.33

Thanks,
Kishori
(615) 253-4734
```
 0
6/13/2006 2:23:53 PM
excel 39879 articles. 2 followers.

0 Replies
694 Views

Similar Articles

[PageSpeed] 31

Similar Artilces:

Compare two spreadsheets, create third with merge
I have one spreadsheet which contains a list of active employees, their payroll file #, and a unique number which indicates their office location (1-5). The 2nd spreadsheet contains the payroll file #, and other information downloaded out of an electronic timesheet program (regular hours, overtime hours, vacation & sick, etc.). The problem is that the timesheet information sheet does not include the employee name and location, so that it can be divided by location and sorted alphabetically. I need to compare the two sheets and create a third that will merge the data. I love Excel a...

pivot chart, mean and standard error
hello, I'm using a pivot table to calculate the mean and the stdevp of values collected in different condition. My problem is to draw the corresponding chart for the means of the values, and using the stdevp values for the error bars. Using a stacked histogram, I have something which resembles to what I would like but I would prefer to have a classical error bar. Is there a way to manage ? I want to keep something automatic and not static, with a copy/paste values to draw manually my charts. Thanks for your help -- Misange migrateuse http://www.excelabo.net mail : http://cerbermail.c...

How to create a report based on multiple queries
Hi all, I am trying to create a report based on 30 queries . These 30 queries are based on one table. When I use the Report wizard and select more than one query I get the message " You have chosen fields from record source which the wizard can't connect. You may have chosen fields from a table and from a query based on that table. If so try choosing fields from only tha table or only the query". I do not know what to do.? Any guidance will help thanks, Dave On Wed, 29 Aug 2007 08:21:02 -0700, davidstevans@gmail.com wrote: >Hi all, > >I am trying to create a rep...

Export Report To Excel
XP, Office 2007 I have a report that when run basically tells us the part number, how many are in stock, and how many we need to make to complete a particular order. There are several order dates to each part, so the rows repeat themselves for each of the order dates. There are total fields on the report that show how many parts need to be made if the customer orders more than what is on hand; all the calculations are done in the report itself. I was able to use a macro to filter everythihg down to show for each part number the first due date for parts that need to be manufactured an...

Report from Access 2003 to 2007 problem
I have converted a database designed in 2003 to 2007. One of the reports unfortunately does not work the same as it did in 2003. The report generates invoices and on the form I had put in a text box with the Control Source property set to [Enter Invoice Date] which brings up a dialog box when the report is run asking the user to put in a date. This works ok in 2003 but comes up as #Error in 2007 in the report generated. In 2007 report design, the text box has a green triangle on it indicating an error "Invalid Control Property: Control Source" & "No such field in t...

Using PERCENTILE with *specific* cells in a column
I am trying to use the PERCENTILE function with specific cells in a column. For example, lets say the column is: A 1 5 2 6 3 7 4 8 Using PERCENTILE on say A1:A3 is easy enough, but I want to do say A1 and A4. I can achieve this by defining a named range such that it would be equal to A1 and A4, but would rather not use named ranges, as the above is a very simplistic example of what I want to do -- using named ranges would require a substantial amount of effort to achieve my end goal. I also realize I could re-order the rows such that I swap A4 with A2, therefore could use perc...

Windows Explorer Columns
Hi, The 'Date Modified' column doesn't appear on any of my folders. I add it, and when i browse away from that folder and return to it, it's disappeared again. How do i get every folder to show that column by default? Thanks, Albert A known Vista annoyance, already solved in Windows7. There're several solutions invlving Registry edit which sets your Template for a specific filetype to what you want, not to MS default - if you Google or Bing for it it should showuo in search results. Sorry I don't have exact URL at hand. it i I know it's f...

How to draw a regression line on 7000+ dated data with Excel 2007
How to draw a regression line on 7000+ dated data using Excel 2007 Are your "dates" actual Excel dates or just text? With actual Excel dates, pre-2007 versions have no problem doing simple linear regression. To interpret the results though, you need to remember how Excel dates are stored: An Excel date is the number of days since 1900, so the numeric value of today's date is 39736, and the intercept of the regression will be the value of the relationship extrapolated to 30Dec1899 (since Excel mistakenly considers 1900 to be a leap year) "Gordon Lee" wrote: >...

Get rid of "folder sizes" and "data file management" links
at the bottom of the folder list? I'd love to remove these to make more space available in the list. Thanks. ...

I get wrong dates when i paste from a different sheet into a new s
Hello again, My problem now is when I open an old excel sheet with data on it and try to paste that info on a new sheet I get wrong information. example: I'm trying to copy dates from an excel sheet I have. The dates are for the year 2004. When I copy the data and paste it on a new sheet the dates I now see are 2000. I'm puzzled. What I find wierd is when I have clipboard open the information shows the correct dates but when it's actually pasted on the sheet it shows as 2000. I'm using Excel 2003, but I get the same thing when using excel 2002(xp) Thanks for your help,...

changed time and date and messed up appointments
Hi I recently went to UK from Australia, then to italy, I must have changed the time zones, and now my appointments are all over the place, Is there a way of getting them back to original times and dates. for the future is there a better way of handling time zones. i also use a pocket pc where i probably changed zones as well. thanks tim See the following MSKB article for a workaround: http://support.microsoft.com/?kbid=197480 -- Jocelyn Fiorello MVP - Outlook *** Messages sent to my e-mail address will NOT be answered -- please reply only to the newsgroup to preserve the mes...

How do I draw the overline for x-bar, average x, or square-root l.
How do I draw the opposite of an underline to delineate the term under the square root sign or to denote 'average x', in words, x-bar inside an excel formula descriptor? Please clarify <excel formula descriptor> In an Excel formula you cannot use symbols but you can in a text file Not sure is overstrike is possible in Excel like it is in Word Bernard "eddie" <eddie@discussions.microsoft.com> wrote in message news:F553903D-41F2-4080-9314-6459CD479139@microsoft.com... > How do I draw the opposite of an underline to delineate the term under the > square r...

Row/column grid lines
Can someone please tell me how to remove the grid lines from a single row or column Cheers Stuart. Hi Stuart, You need to use a fill color. Format>cells>pattern, pick the color closest to the background color. -- John johnf202 at hotmail dot com "Stuart" <anonymous@discussions.microsoft.com> wrote in message news:5CD88DAF-4CDD-4D04-8E9E-A50D9A319672@microsoft.com... > Can someone please tell me how to remove the grid lines from a single row or column. > > Cheers Stuart. Stuart, You can't remove the grid lines from a single row or column. Grid lin...

Cannot get email users email to go to isp email. It goes to exchange server instead.
We are using SBS2003 with EXCH2003. We have branch offices. If I put the branch office in SBS2003 as a user and send an email to the user (i.e. user@user.com) it ends up on the server and never gets sent to the branch office user. I'm using the pop3 connector to connect to our mail provider to send and receive mail. I was wondering if there's a way to bypass the server and have the email go directly to the user at our providers server. The reason why I put the branch office user on the server so that we can have the user in our Global Access List when do a mass email. Please advise. ...

How do I subtract 20% from one column in Excel and place totals i.
I would like to know if someone knows the formula I use to subtract 20% from totals in one column on my spreadsheet to show totals in another column on same spreadsheet. Can anyone help? Spent way to long trying to figure out correct formula. =A30*80% assuming totals in A30 -- HTH RP (remove nothere from the email address if mailing direct) "Full Effect Landscaping" <Full Effect Landscaping@discussions.microsoft.com> wrote in message news:7ACF893B-54DF-4EE4-A931-3BB5FA365821@microsoft.com... > I would like to know if someone knows the formula I use to subtract 2...

Import/Export sql tables to an XML file?
Hi, How can I export/import data from SQL server tables to/from an XML file? Note: I want to tdo this from a desktop application written in c# and uses sql server tables for storing data. Is there a way to do this? Also, are there libraries that already do this? Thanx in advance... ...

How do I sync Signatures and folders between different machines?
I use Outlook on two different machines and the set up is not synched correctly. My home version does not update when I add new folders on the work laptop and the signatures are not saved in the home PC version. The signatures were created on the laptop and saved there. I need help!!!!!!!!!!! What type of mail account? How are you currently attempting to sync? (outlook doesn't sync without the use of third party tools) What version of Outlook? "Ben Davidson" <Ben Davidson@discussions.microsoft.com> wrote in message news:708A9243-C1D4-48FD-84FE-7B37BB49FF99@...

Year + week => first date of week
Hi, I would like Excel to calculate the date for the first day (Monday) in a week.I have my sheet setup like this: Cell = A1 = Year = 2009 Cell = A2 = Week = 32 Cell = A3 =DATE(A3,1,1)+7*B3 this gives me the Excel date = 40038 (2009-08-13 Thursday) this is not correct. Correct Excel date for 2009 week 32 should be 40028 (2008-08-03 Monday). I live in Sweden and we use the ISO week that starts on Mondays (first week of the new year is the first week that contains Thursday). I don't know if it has got something to do with that the formula above does not work. Above formula is the one I ...

Message Rules Get Lost?
When I move my .pst file or restore from backup all the rules no longer work. The Rules are still there, and the folder to which the messages are being moved to is also valid, but the error is that the folder doesn't exist. So I have to go in and re-set the folder parameter for the rule to work. What can I do to prevent this? Or, what can I do to re-initialize all the rules? Thanks, -Bob Outlook 2003 XP Pro You shouldn't have to do that if PST file name & path stay the same. Rules use the full PST name & path for folder reference, at least as far as I know. "B...

Date problem #6
I have a column containing 4/21/2004 10/7/2003 5/5/2004 Which I need to convert to UK dates. because of the variables(4/, 10/) I am unable to use mid etc in seperate columns. also =TEXT(f2,"DD/MM/YY")fails to work. Hi are these values 'real' date values. If yes a simple format should do -- Regards Frank Kabel Frankfurt, Germany "Gerry" <Gerry.Briant@goodrich.com> schrieb im Newsbeitrag news:9a4601c486b0\$01ac1f40\$a501280a@phx.gbl... > I have a column containing > 4/21/2004 > 10/7/2003 > 5/5/2004 > Which I need to convert to UK dates. > bec...

Impacts to Move to Timesheet Reporting
Team members have been reporting work effort on project tasks only through the My Tasks page in EPMS 2007 for about 1-1/2 years. Leadership has asked that I evaluate impacts to moving to the Timesheet function so we can also track Administrative Time. I need direction where to start my research. Articles I could read? Share your experience? Many thanks in advance. -- Carla Evans Presbyterian Health Plan Carla -- You can certain get some "bang for your buck" by using the My Timesheet feature in Project Server 2007. For example, one great benefit is the Plan ...

Tabling
Dear reader(s) I'm doing some coursework using Microsoft excel 97. one of the tasks have to do is use a table. The user enter, a number, and the length o the table will increase. (columns), according to that number The width stays the same. The table has a default length of thre columns. I've been given this notation: = cell above + 1 (a2) = b1 +1 copy formula down Specify row number Choose another cell (b2) stop at 7 b2 =( if cell above = 7 then don't put anything, otherwise add 1 t cell above) I don't know if that will help, it didn't do much for me. Thanks ...

Table
I have the following formula in each of the nine cells in the table below {=TABLE(O17,D29)} Yet each of the calculated amounts are different. I understand what the table is doing I just can't figure out if there are hidden worksheets or cells that show the table. \$268,808,261 9.50% 9.75% 10.00 10.75% 276,201,526 273,122,434 270,197,297 11.00% 271,818,702 268,808,261 265,948,341 11.25% 267,529,883 264,586,412 261,790,114 Please help ...

Pictures in Report
I inserted a banner on top of my report which displays well on the first page but for some reason it shrinks to the left hand corner of the following pages. How do i get the banner picture to maintain it size so each page displays it like the first? "acss" <joekru98@hotmail.com> escreveu na mensagem news:E971FDC6-F2DC-4A4D-87BB-A69AC60A6728@microsoft.com... >I inserted a banner on top of my report which displays well on the first >page > but for some reason it shrinks to the left hand corner of the following > pages. How do i get the banner picture to maintai...

Report Formatting & Writting
Hi Everyone, 1. I am just gettign to grips with writting .qrp reports in RMS and wondered if anyone knows if there is a WYSIWYG editor available to help speed up developement? 2. I want to be able to hightlight figures on reports in red if they are negative - is this possible? Tia 1) Nope, no WYSIWG editor. 2) No, you can't set the color, but you can use a standard VB Format String in the "ColFormat" field of the column definition. Here's teh MSDN Reference on number formats: http://msdn2.microsoft.com/en-us/library/4fb56f4y.aspx Glenn Adams Tiber Creek Consulti...