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.
View 4 RepliesWhat 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)
How can I get EXCEL to display recurring events on one line? In the example below, I want all events of type PLAN to be on the same line.
Phase
Time
Duration
End
[Code].....
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
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?
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 having a problem with a list that I've created. The list is in cell A1, the base data for the list is in the range B1:B50. The problem is that data in this range is dynamic, i.e. it has formulas and depending on the result of these formulae the cells in the range either have a value or the cell is left blank. The problem this causes is that the list ends up having gaps in it because it uses blank cells as well. And this is despite me specifcally ticking " Ignore Blank " in the Data Validation menu where I'm creating the list.
View 2 Replies View RelatedI am creating a spreadsheet that will display the subject mentioned. I need a chart that displays project deliverables as labels along a year long date range. I want to be able to change the date range to any period of time by entering a start date and an end date and having the chart automatically update to that date range and display the project deliverables due during that range. I have used the following links and have been able to accomplish each of them individually, but have not been able to combine the labels of the project timeline link with the dynamic date range described in the second link.
[URL] .......
[URL] ......
How to achieve the spreadsheet I described using the two ideas presented in the link?
I need a shape to adjust its size depending on the start date and end date. So it looks like a graph/timeline. This way i can see who is still working to current date, and what number cantract they are on.
I've tried to do it using an x-y Scatter graph, but couldn't do it. As i'm not sure how to lay out the data
I've attached a non coded spreadsheet to illustrate what i mean, as reading back on this it sounds confusing.
I have a column in which I enter a date, and an adjacent column which automatically enters a sequential number, using ...
View 10 Replies View RelatedI have around 2368 rows for in each column and I have around 8 columns and what I need to do is to remove any gaps. I do not know how to attach picture here, but I can explaining it in words.
A1: 0.9
A2:
A3:
A4:
A5: -0.09
A6:
A7: 0.4
Is there a way to eliminate those gaps (A2, A3, A4, A6...) in one go?
I have a problem ranking a large dataset(more than 30000 rows, 16 different columns need to be ranked). My problem is that I dont want the ranks to have gaps when there are ties.
See how it should be in table below.
Ext P$
Rank
Should be
2,128.34
1
1
[Code]...
I do have a working solution with an array formula similar to this, but it slows down my macro (30 minutes instead of 10 seconds) as I need it to calculate 16 times
Code:
=SUM(1/COUNTIF(A$2:A$35000;A$2:A$35000)*(A$2:A$35000>A2))+1
I was thinking of using a for next loop to rank sorted columns but I dont know how to set it up properly.
I'm wanting to do is drag a formula down and it drop to the next cell rather than the same row number I'm on. For example I'm trying to concatenate a list of phrases whilst changing the main word. Here's an example of the excel sheet
Base Terms
Phrase
Result
car
red
van
blue
bus
red
blue
There is meant to be a space after the second red and blue enabling me to make (in order), red car, blue car, car red, car blue
How can I make it so I've done the relevant concatenate formulas for A2 with the B column and simply drag it down and Excel will switch from A2 to A3 and so on when I've dragged out the 4 formulas?
I have a file that contains addresses in column C. I need to find any gaps in addresses.
Ex:
211 Corbin Dr
213 Corbin Drive
214 Corbin Drive
123 Apple Drive
124 Apple Dr
124 Apple Dr
127 Apple Drive
I want to identify that there is a gap between 211 Corbin and 213 Corbin and 124 and 127 Apple Drive.
Currently the address including house number are in column c. However, I split the house number into a separate column and the street address in yet another column. There are also duplicates which I have identified by using conditional formatting to highlight the duplicates.
I have a sheet of over 40000 rows, I attach a sample. Column a is called dam and column b is called damsire. Each dam has only one 1 damsire. Both column a and column b are sorted ascending. Unfortunately there are big gaps in column b. some of these can be filled in as we have the information e.g. b38 and b39 should be Manila..as the row 37 tells you the correct damsire. Similiarly b49 could be filled in as shernazar. I want to create a new column which contains a formula to fill in these blanks. of course some of the blanks cant be filled in as the information is not there e.g. b23 to b28.
View 3 Replies View RelatedI'm trying to get Excel 2010 to automatically generate a custom timeline in a separate tab using data I enter in a different tab. In the attached example, the data in the first tab ("daily log") tracks the number of trucks parked at a certain yard by the state they are registered in, their company, and their ID number. I would like Excel to automatically generate the timeline I've created by hand in the second tab ("Timeline (desired outcome)"). I need a formula that returns a "1" if several conditions are met (see comments in example) and a " " if those conditions are not met. I played around with IF(AND...) functions, but I've never used them before and couldn't make them work. There are a few complicating factors: there aren't a consistent number of trucks at the yard per day, and some trucks changed their ID numbers during the data period. I use a blank row, highlighted in dark grey, to visually differentiate days. This row can be filtered out with the "null" column.
View 4 Replies View RelatedI'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...
how can I change a bar graph horizontal time line from ascending to descending?
View 1 Replies View RelatedI have been trying to formulate a condition to fill up my timeline by checking the start and end times and date of jobs.
In my attached worksheet, I have an example (which was done manually) what I would expect the timeline to look.
[URL]...
For example,
SLIS900H has a start of 6/8/2014 11:54:05 AM and end of 6/8/2014 12:03:15 PM
As such, the TimeLine entries 06/08/14 11:00 and 06/08/14 12:00 would be shaded (or at least have some indication that it is to be shaded). After which, the Main Task from which SLIS900H belongs to (TSOH Extraction), would also have its Timeline shaded.
I have a load of postcodes over 8 different tabs, the problem is the format of the postcode is wrong. I basically need to delete the first gap of each cell to make the postcode valid -
DL 7 9
DL 8 1
DL 8 2
DL 8 4
DL 8 5
DL 9 3
DL 9 4
DL 10 4
DL 10 5
DL 10 6
DL 10 7
DL 11 7
You see I need to have it like DL7 9, or DL10 7, but i'm not sure how, I've attached the file so you can have a look.
I'm doing some simple data extraction, e.g.
A B
1 bob 3
2 mandy 4
3 charlie 6
4 dave 1
5 steve 5
So I had in c1 to c5 = =if(b1 > 3, a1,"") autofilled, which works fine, but I end up with,
gap
mandy
charlie
gap
steve
how would I get,
mandy
charlie
steve
also is it possible to have an if statement in 1 cell change the value of another cell?
e.g.
in a1
if(b1>5, c1="yes",c1="no"), can't seem to get it to work
Is there a formula to count gaps? If you see the sheet below, I want to count maximum gaps in range A1:J12 and put that count in column L.
******** ******************** ************************************************************************>Microsoft Excel - Book2___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutL12=ABCDEFGHIJKL1X XX XXX 22X X X 33 X X 64X X 85X X X X 26 X X 47 X X 48 X X 59 X X 610 1011 X X X X X 112X 9Sheet1 [HtmlMaker 2.42]
To see the formula in the cells just click on the cells hyperlink or click the Name box. DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
I am trying to get two shapes to butt up to each other. Unfortunately the shapes either leave a small gap or a slight overlay. I have tried using Ctrl + arrow key to move in small increments, but that didn't work. I have also tried adjusting the width of the rows, but the rows jump backwards or forwards to a number instead of staying with the number I entered. I want to create a seamless shape out of many different shapes.
View 6 Replies View RelatedI have what is probably a simple problem for most, but can't figure out what to do.
In the sample sheet attached, I have times in column E, and an action describing what has happened in column G. What I want to do is calculate the length of time between an opening action and closing one, but don't know how to go about it, as there can be an empty cell(and sometimes more) between each open and close.
Rather than manually typing the time in 5 min increments across a range of cells, i want to have a start time, then the next cell add 5 mins. EG: 09:00 next cell 09:05 and so on.
View 7 Replies View Relatedmy excel sheet runs through a lot of calculations, opens Flowmaster, a simulations program, passes on data, receivs data and so on. Is there any way to have a user input to stop the whole simulation. During the first tries I had a lot of break point in my debugger. But now I want to have a button to hit or better just some keys to hit to stop it without using the ctrl+alt+del which closes everything.
View 4 Replies View RelatedI have attached a work sheet where I have part of a formula working.
Although what I am trying to achieve is in the example in column B.
It is possible to fill in the gaps as in column B with the task between the time frames.?
The solution can be either in VBA or conditional formatting, if possible.I have product names on column A and weeks as from column B where I have the quantity sold. So, every week I'll have an additional column.
A B C D E ...
Product Week1 Week2 Week3 Week4...
What I need:
If the cell is filled, highlight it in green.
If the gap (empty cells) between weeks is =1, highlight it in yellow
If the gap (empty cells) between weeks is >1 but <2, highlight it in orange
If the gap (empty cells) between weeks is >2, highlight it in red
The attached example better illustrates the needs : Example.xlsx
I get given a csv file on a monthly basis which contains consumption data per day for the specified period. This sounds simple but on occasion (more often than not) the data has missing days. This can cause me problem later on in my analysis.
I can happily total the monthly consumption using the date and month text. What i want to do however is to sort the csv file into daily consumption and highlight the missing days i.e. have a range of the days in the month and allocate the daily data to the correct date. I currently do this manually but know that there must be a better, automated approach... searching for matching dates for example?
In my head i'm thinking the following approach but lack the coding skills to do it.
1. Define the start and end dates. Perhaps count the number of days between the two dates and autofill the start date down the appropriate number of days in column A?
2. Paste the csv file into a different sheet and, starting from the top, cut and paste the csv data to the correct date created in step 1. Do this for each row based on the csv data.