I tried so long I finally gave up and drew this by hand in MS Word:
But I wonder if you guys could help me chart this automatical in Excel.
I want to chart the time period a ship (Vela, Serpentine, Rainbow etc) are contracted. I want a specific color for the firm contract, and another for the remaining optional contract. If a ship is under construction I also want a bar showing when it's done.
Each ship has a rate. I want this written on the bar. In "Vela's" case there are to rates for the same contract: The first 740 days the rate is 24,5 the rest of the period it is 29. I also would like to be able plot an additional contract for the same ship for example after "Syrena" is done in yr 2009.
I'm attempting to chart data obtained from our phone system to see when users are logged in/on rest.
I'm using Excel 2007.
The data we extract has: person | status | start | finish | duration
Status is either "login" or "rest" Start/finish is time (h:mm) Duration is a formula (h:mm, finish - start)
I want to have a stacked bar chart that shows when a person was logged in/on rest; one bar for each person.
x-axis: person y-axis: time (8am - 6pm) legend: status
The problem I'm encountering is that each person logs in and out multiple times throughout the day (therefore, has multiple lines to their name in the data), and I can't get this information to appear on a single bar for each person.
I've sorted the data into a pivot table which gives me the information I want, but how to get the chart.
Row labels a. Person b. status c. start
Values Min of duration
Ideally, I would like to avoid using a Gantt chart within cells (as it would involve vlookup/if statements), but am beginning to think that's my only option...
I have a spreadsheet where users can enter a time using the 24 hour clock - these times are random and can be anything within the 24 hour period.
I want to create a chart or graph that has a timeline of 24 hours, (in multiples of 1 hour starting at 07:00 and going right through to 06:00), and I need the times entered by the users to appear on the chart to give an overview of when these incidents are happening most.
I was thinking that the cluster or scatter chart might work but I can't figure it out. I don't actually need both X and Y Axis, just a timeline - is it possible?
The below is a sample Gantt chart that I'm using, it's working fine, what I want to do is when the STATUS is completed (Column H) goes from a cross to a tick (I'm using the format of Wingdings 2 - cross is O and tick is P) is to change the Gantt chart colour from it's current colour of green to say orange. In conditional formatting (in cell I3) I have, formula is =AND(I$2>=$F3,I$2
Hi guys, I would like to be able to automatically gantt chart the following data table, my problem is that i am unaware of how to create the gantt chart when there are several entries for the one location - the table is a booking sheet and the gantt chart is just a graphical display of the activity occuring in each bay - if any could help me achieve the following gantt chart from the the following table it would be much appreciated: ...
I am trying to find a way to automate this crude gant chart. What I am trying to do is the following:
1: By entering a start date and due date, and by adding a percentage to 4 different goals, create a sort of gant chart for each job.
2: Have this chart update as the start and due dates change.
3: Be able to past other gant charts below this one, based on the leader assigned to each job. ie. if Dave is a leader, create a worksheet that will have all of dave's Gant charts in one workbook, on one worksheet, pasted each one below the next. The goal being we can give each leader 1 sheet that has all their jobs.
Someone else built a this timeline using a date calculation that I am unable to modify. Fix date formula in row 7 to show FY14-FY18 and in row 7 show the quarters 1-4 along the years.
Hey Guys- I'm not sure if this is even possible but I need some help. I have attached a file below similar to a gantt chart. I need help with the conditional formatting, as I have manually changed the colors to match what I need automated.
Basically I have 6 tasks and I need to change dates for individual projects but I also need the corresponding color to also change.
I am developing a Excel gantt chat i have chosen to display the bars by the lenght of the cell because the data is also most real time so as the time reduces so will the bars however the problem comes when there is tasks being done under one above it as i cant have the cells with different widths
What would be the best way to get around it as i want to keep the almost and the appearance of the real time nature of the cell width equal to the value.
I have created a pseudo Gantt chart in Excel (a horizontal stacked bar chart). The chart is driven off data I have in a Power Pivot model (Auto Refreshed) and the Gantt chart displays data which spans a fixed 12 hour time frame.
The problem I am having is that in order for me to get the chart to display correctly I must manually (daily) get the excel decimal values for my Minimum DateTime and Maximum DateTime and then enter those numbers into the "AXIS Options" Bounds Minimum and Maximum value fields. I would like to automate that process so that the Axis Bounds are either driven from the data cells OR are always set to a specific 12 hour window 6pm->6am daily.
I am looking to create a spreadheet that will automatically shade blocks in a gannt chart according to a list of dates.
I.e B13 & B14 will have the start and end date of the particular task or activity (this will be on another sheet)
Row A3 - S3 will contain dates, iusing a lookup formulas excel will look up the activity name (or number) and look for the relevant date, if the date listed in row 3 is within or equal to that date it will return yes, if not a no.
this seemed straightforad, but I cant think of a consistent formula that will do this, without having to keep changing it ...
I'm trying to create an Employee Scheduler. I want my employees (1 technician per row for a total of 10 rows) in the 1st column then the monthly calendar to the right (1 cell/day). That is what you'll see when you open the worksheet. Then, down below I set up my Data Area where I input my project, start date, end date, technician (where each tech is assigned a unique number), and other data across a row of cells. This could be infinite (or at least up to the very last row).
I modified a free Project Scheduler I got from XL-EasyGantt to become my Employee Scheduler but am having some difficulties getting it to do what I want. The project Scheduler basically works by entering (on the same row) the project (or task), the start date and finish date and then the cells get automatically filled in across those dates. This allows for overlapping of tasks but I want to create an Employee Scheduler so it doesn't allow overlapping or schedule a technician to start a new project before he/she has completed the one their on.
The main function which is in each cell of the monthly calendar is as follows: =IF(OR(AND($G28>=K$4,$G28<L$4),AND($M28>=K$4,K$4>=$G28)),IF(AND($E11="x",$M28>=K$4,$M28<L$4),"x",IF($E11=".",".","..")),"")
My problem: I want to be able to look at the Main Schedule and see when each Technician is scheduled for a job (or multiple jobs) for any given month. I want the function to look in my Data Area and assign the start date and end date of a project and the corresponding technician number and fill in the date cells across from their name in the Schedule above.
I'm using a stacked bar chart (in Excel 2010, running on Windows 7) to create a simple Gantt-type chart - with just four or five bars. I've got my chart looking most of the way I want it to, but one thing still eludes me: I'd like to set up the major axis ticks to be quarters of the year (from 1/1/2011 to 4/1/2013). Since quarters are not regular intervals (they are not exactly every 90 days), I can't do this using Excel's standard functionality for choosing axis tick marks.
I'm trying to create a simplified Gantt chart of sorts, and cannot figure out one piece of it. I'm not sure how to write out exactly what I need, but here goes.
I would like to have a formula that looks at the percent completed (which the user inputs) and multiplies it by the total duration for the task to give a total number of days completed. Then, under the corresponding dates, the color of the cell would change. I've attached a simplified version of what I'm talking about.
On the attached spreadsheet, the total duration (D2) is 5 days and the percent completed (C2) is 40%. This calculates to 2 days. With that said, I would like the cells under the first 2 days under the listed dates (E2 and F2) to change color. When the percentage complete reaches 60%, then G2 would change color, when it reaches 80%, then H2 would change, and when it reaches 100%, then I2 would change.
My guess is that the formula will need to result in a particular value, and then I'll use Conditional Formatting to do the actual changing of the cell color. Assuming this is correct, I still don't know what formula to use to accomplish this.
I have a spreadsheet created in Excel 2003 (which is what we use at work, unfortunately).
My employees periodically take a test to ensure they have certain items memorized (or are making progress to that end). The spreadsheet rows show all 46 of my employees, and their test scores. The columns are the dates that the tests are administered. I can create a line graph based on the chart data, and interpolate these data with no problems.
The problem is that there are 46 employees! 46 lines on the same graph make for a very cluttered, hard to understand visual. I want to simplify the view by "filtering out" some of the data.
I have an additional column in my spreadsheet for each employee's work area (Area 1, Area 2, etc), and another column with data based on first letter of last name (the values here could be "A-G", "H-M", "N-S" and "T-Z", for example). I figure i could filter my line graph based on these two columns. For example, somehow select just Area 1, and reduce the number of lines on the graph to 16. Or better yet, Choose "Area 2" AND "A-M" and end up with 7 employees (and therefore 7 lines on the graph).
Here's what i have tried:
1) Select the work area column, and use the Filter, which created a drop-down list at the column heading. When i use this drop-down list, i can easily filter the data in the worksheet by Work Area, but this is not reflected in the line graph, which still shows all 46 lines. The problem was that i forgot that i had set the Calculation Options to "Manual". Setting this to "Automatic" (or leaving it on Manual and pressing F9) solved the problem, as the chart now updates when i use the filters. Calculation options are under the "Formula" tab in 2007, or in Tools -> Options -> [either calculation or formula, i forget what it's called] in 2003.
2) Create several separate line graphs in several separate sheets. I wouldn't want to assign someone else the task of maintaining a spreadsheet of such inefficient design.
Trying to create an excel chart to create totals based upon different keys. I need to be able to calculate how many customers there are by Manager and then By Rep. Then to figure out how many were New, Current, Total # of RSVP and attended for that Rep. Below is how I have started but I am having some problems getting certain parts. I know when I get one the rest will fall into place. I can calculate how many total customers by manager and by rep just by doing a Countif command but how do I determine the # of New, Current etc. Is there a If Than command? Managers Totals are simply his reps totals.
Example.. A B C D E F G 1 Manager Rep Customer New Biz Current # RSVP # Actual Attend
Need totals to look something like this....
A B C D E F G 1 # of Cust # New # Current #RSVP # Actual Attend 2 Manager 3 Rep 1 4 Rep 2
What would be the VBA code to put a border line on a row that that would separate the data that is due before today, from the data that is due after today?
This is a big data set, I have a loop that will do it, but it takes forever. Is there some way to do it faster with a filter, or conditional formatting, within VBA?
Example today is 8/22/09.
Would need a border line that would look like this...
I have sheet in which cell A1 contain value in alphanumeric format like D815327985.
Cell B1 contain time as 21:00hrs and cell C1 as 21:03 hrs.
cell C1 has formula B1+30/1440 which give me time 21:30.
I am looking for macro or formula which would pop up or give red colour when cell C1 reach at 21:20 hrs.
To make it easy, i would like to explain my problem in a simple way because from longtime i am not getting any satisfactory reply from message board.
I have 1 ticket number that is D815327985 in Cell A1.
Once i received this ticket i enter the current time in Cell B1. (I have taken time here 21:00 hrs for more convieniance).
Cell C1 formula give me the timeline that is within how much time i have to give follow up on this ticket.
I want formula or macro for C1 cell which would pop up or gives red colour when its reach to 20 min time of B1 cell.
Find the data which entered in my sheet
A1 D815327985 B1 21:00 C1 21:30 (here is formula B1+30/1440) (And i want here is this cell would get red colour or pop up when its reach to time 21:20 hrs)
I have numerous sheets i am working with as schedules , would it be possible to create a timeline that would show you where you currently are (time of day ) just a simple line or similar that stands out , my times in Row 4 are split into 6 hour intervals (06:00,12:00,18:00,00:00) with the date above in Row 3
how I can format this timeline better (it was a to,e;ome template created by someone smart on this forum) - so that there isn't a huge gap between 1892 and then 1977... and then so the rest of the data isn't scrunched together.
I am trying to create a pivot chart that show the average response times in hour but for some reason my charts looks not right. The axis show a max of 1 but the graph itself showed us up to 17. I think easier to explain if I attached the worksheet.
I've used trend lines to find the mean and average but for some reason I have not been able to set an upper and lower limit. without introducing a new column.
I have two items: Written Premium and Earned Premium. Each of these items has a forecast and actual number for two months. How can I create a line chart (with dots) that will show both of these items. I've attached a spreadsheet.
Create a well labelled pie chart on a separate worksheet to show the profit made by each of the Liverpool departments to the overall profit for 2006. On the labels show the percentage contributed by each department.
I want to create a pie chart in Excel 2007. The data is not next to each other in the columns. How do I create a chart so that it includes the legends as the various departments and the data shown as the figures given under 2006?
