LinkBack URL
About LinkBacks
Is it possible to create a timesheet chart like this
http://img200.imageshack.us/img200/1292/timesheet.png
using excel .I could see example in the web to create Gantt chart but does excel provide means to create charts like this.
designing a timeline chart
Is it possible to create a timesheet chart like this
http://img200.imageshack.us/img200/1292/timesheet.png
using excel .I could see example in the web to create Gantt chart but does excel provide means to create charts like this.
post you data as we need to see how you define the multiple time slots.
in principle each part of the stack is just another column of minute values.
I would like to display a timeline graph indicating whether components are ready or not for any given time. For .e.g. for 07/12,
Component, start,end,duration(hrs),state
c1,07/12/2009 6:00 AM,07/12/2009 12:00 PM,6,Ready
c2,07/12/2009 6:00 AM,07/12/2009 10:00 AM,4,Ready
c1,07/12/2009 12:01 PM,07/12/2009 5.00 PM,5,Not ready
c2,07/12/2009 10:00 AM,07/12/2009 6:00 PM,8,Ready
....
and so on
components can be Ci where i=1,2,3....
They alternate between ready and not ready state.
Here is how I would like output to look like. The color should be red for 'not ready' state and green for 'ready' state.
re: designing a timeline chart
Sir, Thanks again for your help so far.
constructing your data layout is the tough part of your problem.
it maybe possible with formula but I think vba will problably be needed.
The attached show you how the data should be laid out. Stuff in italics is made up as you did not post enough sample data.
re: designing a timeline chart
Thank your Sir for your effort
I will have a look at your attachment and post if I have questions tomorrow as it is late in the day. Thanks again.
cheers
Last edited by pradeepbm; 08-07-2009 at 02:28 PM. Reason: corrected typos
VBA means using code to generate a summary table.
Is it possible to use VBA to automate the creation of chart. The number of ready/notready state is not fixed and it can vary. Would it be possible that chart adjust automatically when the no. of columns changes. Please let me know. Thanks Pradeep
Yes it's possible.
If you need more help can you post a more complete set of example data.
Like always you are helpful Sir. I am attaching an complex data set. My Java code will generate the data in the format shown. As I mentioned before there can be any number of columns ready/notready. I want the chart to redrawn by itself ie. automated and also the axis adjusted so that data for relevant time is shown. Also color should be adjusted accordingly.
Please Login or Register to view this content.
Thanks Sir. Working great.
Is it possible to modify the chart so that x-axis (horizontal) starts at the least start date, in my example, that would be 8/6/2009 6 AM. And also have an end, I (my code) could provide this in the excel sheet. This makes it possible for the viewer to get better view of the state in the time frame he/she is interested in.
My java code writes to the excel file. Is it possible to link (attach) the code you have given so that when someone open the file in MS excel the chart gets displayed. Hope I am not asking for too much.
You can place some code in the workbook open event to run code on openning.Please Login or Register to view this content.
Can please let me know , how do I do that ? I don't have much knowledge on excel. Thanks again for making axis adjustment. Great work Sir.Originally Posted by Andy Pope
In the Thisworkbook object you would add code to the Workbook_open event
This assumes your data is already in the workbook.Please Login or Register to view this content.
re: designing a timeline chart
Perfect
I am using JExcel API to populate the sheet. Now everything is automated. Thanks a lot.
If I have too many columns the color code for legends are no longer working. Red for not-ready,green for ready,yellow for unknown .I am getting colors other than these three.Can you pls help me Sir. I have attached the file.
Interesting.
Try this variation.
Please Login or Register to view this content.
re: designing a timeline chart
Working
One more thing - Is it possible to display the last time stamp in the axis. The last[end] value in the x-axis is not displayed.
Excel has a palete of 56 colours. In this case that is not the problem.
There appeared to be some sort of redrawing/updating issue.
The only way to get the last axis label displayed is to have a Minimum and Major step value that will generate the Maximum Value.
The result of the following formula needs to be 0, or very small, in order for the last point to be displayed
=MOD(MaximumScale-MinimumScale,MajorStep)
re: designing a timeline chart
Sorry Sir. I have to bother you.I am getting error message when I open the book.
Because you now have a full set of 256 columns of data.
When it tries to reference column IW it bombs.
Add this new line to the code
Please Login or Register to view this content.
Working pefect Sir.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks