Possible to chart data for dates implicit within a range?

I have a list of computer programs with start and end dates for each. I need 
to determine how many programs were running on my system on any given day. 
Can I get this data for individual days between the start and end dates 
without creating columns for each intermediate day? Ex.: Program 1 ran from 
01/01/2005 to 01/14/2005. Program 2 ran from 01/06/2005 to 01/22/2005. Is it 
possible to chart the total number of programs running on 01/08/2005 without 
manually creating a column for that date?

Thanks,
Nechama
0
Nechama (1)
9/1/2005 2:59:04 PM
excel.charting 18370 articles. 0 followers. Follow

1 Replies
961 Views

Similar Articles

[PageSpeed] 48

You can compute this with formulas. Star with data in A1:C4 as follows:

           Start        End
Prog A   1/1/2005   1/14/2005
Prog B   1/6/2005   1/22/2005
Prog C  1/10/2005   1/20/2005

In E1 place the data in question (1/8/2005). In F1 enter this formula:

=SUMPRODUCT((B2:B4<=E1)*(C2:C4>=E1))

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______


Nechama wrote:

> I have a list of computer programs with start and end dates for each. I need 
> to determine how many programs were running on my system on any given day. 
> Can I get this data for individual days between the start and end dates 
> without creating columns for each intermediate day? Ex.: Program 1 ran from 
> 01/01/2005 to 01/14/2005. Program 2 ran from 01/06/2005 to 01/22/2005. Is it 
> possible to chart the total number of programs running on 01/08/2005 without 
> manually creating a column for that date?
> 
> Thanks,
> Nechama
0
9/1/2005 9:11:00 PM
Reply:

Similar Artilces:

How to Gather TimeSheet Data to Create Invoice
Hello, I am building an employee timesheet database that will also be utilized as an invoicing database. Whtat I have is a table/form setup in which a user enters in an employees time. The employee will spend the day on various different projects and will write their time sheet accordingly. The time sheet table/form stores the Employee Name (EmployeeID), the job they were working on (ProjectID), the hours worked on that project (HoursWorked), and the cost code for the time spent (CostCode....their are 50 different cost codes depending on what the employee was doing such as calculations, co...

Countif over a range of cells for various codes.
Hello, I am trying to get a single referance cell for how many holidays I have on a day in a holiday sheet. I have 22 different employees all with their own area on the same worksheet, and I have a master section where I am trying to insert a countif function with arguments, but at present am having no joy. I was trying "=IF(H,1,0)(SUM(E8,E13,E18,E23,E28,E33,E38,E43,E48,E53,E58,E63,E68,E73,E78,E83,E88,E93,E98,E103,E108,E113))" to see if I could get a cell to work out how many holidays I have in a day, but would also want to expand this to use about 6 or 7 other refera...

can I merge birth dates on calendars
i'd like to print candars yearly for my family with all birthdays included, but without re-typing them ...

Outlook data files and users
Due to a crash, I have a mix of user IDs and I would like to consolidate them to one. In Outlook it appears I was able to find my current e-mail, but the contacts and calendar information seems to be in another identity. I seem to have found most stuff, but I am a little confused as I have an inbox in "Personal Folders" and another one in "Outlook Today [Personal Folders]. They do not have the same data. I would like to consolidate them I would also like to have a list of whatever data and configuration files Outlook uses so I can do some additional huntin...

Data validation and Freeze Panes
Hi, I have some cells with data validation with In-cell dropdown selected. I notice that on a laptop with windowsXP/office 97, that the validation dropdown does not operate for any cells with this style of validation ABOVE the freeze pane section, although it does on a desktop PC with office 2000 . The validation dropdown works fine BELOW the window freeze pane section. Just not ABOVE.. Any help here? Rob Rob, It is a known bug in Excel 97. There is no fix for it. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com chip@cpearson.com &q...

macro date entry
I have a series of reports that are based on entering some date parameters. They are for each employee. I know how to set up a macro to run them all, but how can I set it up so that it asks for the beginning and ending date at the very beginning and then applies this parameter to all the reports? Each of these reports is based on a query. I really need to know ASAP on this one. On Fri, 18 May 2007 12:02:00 -0700, Steve Y <SteveY@discussions.microsoft.com> wrote: >I have a series of reports that are based on entering some date parameters. >They are for each employee. I ...

error bars on bar chart in Excel 2007
I would like to assign a separate error bar value to each of the data sets within my bar chart. When I use the customise error bars feature it allocates the same error bar to all of the bars in the graph. Each chart in the bar is a mean in itself, hence the requirement for individual error bars. I see from previous posts that this appeared to be possible in Excel 2003 but I don't have access to this version. If each bar represents the average of a set of numbers, then you can calculate the standard deviation (or other measure of dispersion) for this set of numbers. Put these ca...

Displaying Map data
Have created a map from data in Excel file, but need to display data from (4) columns, and there are only (3) display formats available - category shading - dot density - graduated symbol Is there a solution I'm not aware of? All help appreciated! Sandi ...

Data Validation #13
I am attempting to use an AND formula to limit the input in a certain cell to a specified range (e.g.=AND(a1>100, a1<200). The cell is formatted for percentage. If I input an invalid entry my message appears and if I click retry and input a valid entry I lose the percentage sign and the message reappears. Any suggestions. Thanks in advance. --- Message posted from http://www.ExcelForum.com/ Maybe you can use: Allow: Decimal Data: Between Minimum: 1 Maximum: 2 This seems to be forgiving when the cell is formatted as percent. ASPENCO wrote: > > I am attempting t...

How to chart a series of formulas?
Hi, does anyone know how to chart a series of formulas? I currently chart cells in column C. Each cell in column C contains a formula - "=A1+B1", "=A2+B2", "=A3+B3", etc. I would like to do away with column C and just get the chart to calculate the values that are being displayed automatically. How can you do this? Thanks Marcus - Charts aren't that smart, and worksheet ranges are pretty cheap. Compute the data in the worksheet, and plot the column with the formulas. Of course, you could name your ranges (Adata and Bdata) by selecting each range, ty...

Exchange 2003
Is there a way in exchange 2003, to query all mailboxes for: ASX/ASF/MPG/MPEG/AVI/QT/RM/RAM - movie file formats. And have exchange 2003 which we run . . to generate a detailed report showing how much data of the total information store that movie files are taking up(eg: if employees are using the mail system appropriately). Also if it can, once it queries which users have movie format files in their inbox, to then list how much of the overall information store is being used by each employee(movie formats). ie: user1: 2% of the organizations total storage data is from this users total ...

How can I check a cell for current date and insert it if blank?
I am modifying the invoice template. I want to create a formaula that checks the date cell for the current date. If a date occupies the cell nothing happens, otherwise, the current date is inserted. Can this be done? Thanks Don Sub insertdate() With ActiveCell If Not IsDate(.Value) Then .Value = Date End With -- Don Guillett SalesAid Software dguillett1@austin.rr.com "Don K" <Don K@discussions.microsoft.com> wrote in message news:85927992-1BA5-4B6A-94A7-AFFCCB607BD7@microsoft.com... >I am modifying the invoice template. I want to create a formaula that >checks >...

character restrictions when importing data from a text file
every time i import a text file into excel, only the first 255 characters are being imported. how can i solve this problem so that all of the info in each field is displayed regardless of the number of characters? p.s. i'm using excel 2000 I've never had that trouble when I used File|Open and imported the text file. How are you importing the file? richtea wrote: > > every time i import a text file into excel, only the first 255 characters are > being imported. how can i solve this problem so that all of the info in each > field is displayed regardless of the number o...

VBA and Charting Named Ranges?
Our developers write out a .csv file, with row 1 being the strings for the column titles. I can read these .csv files into a worksheet, so that column 1, for example is titled "LogTime", and Column 10 is TimeOnDisk. I can create a named range for each that covers just the cells that are populated e.g.($A$2:$A$2280). I can use the Names drop-down to verify the named ranges are correct. I have to do this, because in the next release, the developers may add or delete columns of data, but have promised to keep the column names. And the number of rows varies by the number of days worth of...

requesting data from exchange server #2
I have this user that is having severe problems with outlok 2003 in which she clicks on address book and the whole thing freezes and says requesting data from exchange server. outlook is completely frozen. any idea why? other users are not reporting this issue nobody <nobody@nobody.com> wrote: > I have this user that is having severe problems with outlok 2003 in > which she clicks on address book and the whole thing freezes and says > requesting data from exchange server. > outlook is completely frozen. any idea why? other users are not > reporting this issue Call...

How do I get megastat menu for statistical data?
I am in a statistics class and the teacher has shown us a menu on his Excel called Megastat (Mega Stat?). We have homework in this and I don't have this on my Excel, how and where can I get this? Mdmisti - > I am in a statistics class and the teacher has shown us a menu on his > Excel called Megastat (Mega Stat?). We have homework in this and I don't > have this on my Excel, how and where can I get this? < A Google search indicates it's an add-in that comes with some statistics textbooks from McGraw-Hill. Maybe the software is on a CD inside the back cover of ...

How do I specify x-axis data for a logarithmic line graph?
I have a 2 sets of data that I want to graph in a logarithmic line graph where the y-axis is logaritmic and the x-axis is arithmetic. I essentially have coordinates (x,y) that I want to plot, but excel automatically graphs my selected coordinates as two series' of range values, and they take the corresponding spreadsheet row number as the x-axis values. You have made a Line chart when an XY chart was needed. A very common mistake caused by the odd name "line chart" best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "kculerie" <...

Help Programming an XY scatter Chart #2
I have a user form that inputs data onto a spreadsheet. I have a command button that runs a macro that activates the sheet, selects the data, and creates the XY Scatter chart. My Problem is that I don't know how to set a loop to set each series' properties . (The number of series changes every time) Bellow is an example where I manually set the properties for three of the series. Any Ideas on how to create a loop to set all. Range("A:A,C:C,D:D").Select Range("D1").Activate Charts.Add ActiveChart.ChartType = xlXYScatter ActiveChart.SetSourceData Sour...

Query for most recent date
I'm trying to pull the most recent versions of data from a history table. Each row contains the item being modified, the date the change was made, what the change was, and which user made it. For each item being modified I want the most recent change, if I only have the itemID and DateChanged columns in the query I can do it by enabling the Total row in the query builder, and set the itemID to groupby and dateChanged to Max. I also want to show the changeTypeID and UserID fields for the change identified by the previous two fields, but none of the options in the Total dropdown appear ...

Date Entered,Only Number Symbols Appear
I created an hours sheet and about a week after creating it I needed to add another day. When I type in 7/31/04 and hit enter what appears is ######. What is happening? Thanks Mike. On Sat, 17 Jul 2004 18:38:01 -0700, "mikeo56" <mikeo56@discussions.microsoft.com> wrote: >I created an hours sheet and about a week after creating it I needed to add another day. When I type in 7/31/04 and hit enter what appears is ######. What is happening? >Thanks Mike. Maybe the column is too narrow? --ron Hi Mike! Sounds like the column width isn't wide enough to display the dat...

Highligt a Date in Project 2003?
I have many Milestones that are very important in my Project, how can I highligt these Milestones over all the project? When I used to make Grantt charts in Excel I used to put a vertical line over all the projet but with Project 2003 if you use a line the line moves from the original position. Is there a way to highligt a Miliestone? Thank you In article <7F025E7D-568D-4ADC-8275-3AA272040F09@microsoft.com>, J. Casas <J. Casas@discussions.microsoft.com> wrote: > I have many Milestones that are very important in my Project, how can I > highligt these Miles...

Counting dates using "more than"
Hi all This is hard to explain so please bare with me I would like to count in a column with dates. The formula should count or group the periods when the gap between th dates are more than 3. (days) 1-May-04 4-May-04 5-May-04 6-May-04 7-May-04 11-May-04 14-May-04 15-May-04 19-May-04 In other words; 1,4,5,6,7 is one period 11,14,15 is another and 19 is another With the total being 3 Thanks in advance Joey:confused -- Message posted from http://www.ExcelForum.com Assuming the dates are always in sequential order and the range of dates in this case is A1:A9: =IF(SUMPRODUCT(--(A2:A10-A...

PowerPoint 2007 Org Chart
Do you have any training on how to do an org chart in 2007 PowerPoint? The online training is inadequate in reference to org charts. Loved 2003 but so far hate 2007. -- Sunsen Hi I found quite a lot of info on the MS website - but you may not have found it as they have sneakily renamed org charts. They are now part of SmartArt... There is a training course here: http://office.microsoft.com/training/training.aspx?AssetID=RC101772971033 And SmartArt related stuff here: http://office.microsoft.com/en-us/powerpoint/CH100675761033.aspx If you have a particular question, l...

Multiple Colors on Area Charts
I would like to know if it is possible to have two different colors in the plotted area of an area chart. In the end, I would like to have the color for numbers less than 0 be one color and greater than 0 be second color. Thanks Hi Have a look at Jon's page on horizontal bands. http://peltiertech.com/Excel/ChartsHowTo/HorizBands.html Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "SarahR." <SarahR.@discussions.microsoft.com> wrote in message news:A77861A3-B9B7-4926-BA1F-9FBE14BE03B4@microsoft.com... >I would like to know if it is pos...

how to create a line-chart with 2 sets of data who has different x
hi, i don't know how to create a line-chart with 2 sets of data, they have different size. Say set_1 has x-axe from 0 - 12 with 150 intervals. Set_2 has x-axe from 0-14 with 130 intervals. How to put them into a single line chart? Make an XY chart instead. You can connect the markers with lines in both types of charts. http://peltiertech.com/WordPress/2008/07/10/line-charts-vs-xy-charts/ - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Frank_C" <Frank_C@discussions.microsoft...