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)
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
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 Replies View RelatedI 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 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'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.
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 RelatedHow to use conditional formatting in Excel 2007 (). I'm creating a spreadsheet to track my group's timeline for completing strategy meetings with our customers. When we have a meeting, I will enter the date of that meeting in a specific cell. In a successive column(s), I'd like for a cell(s) to turn a specific color based on the length of time from the last meeting (or if there is no date for a meeting entered - i.e. no initial meeting has been set).
View 9 Replies View RelatedI just got into the world of PowerPivot, Excel 2013 and Pivot Tables and am in the process of creating a Dashboard which I will then be uploading to SharePoint 2013.
On top of page I added the new timeline filter which I've linked to my pivot charts. Now what I would like to do, is create an additional pivot chart which looks at whatever date range has been used in the timeline filter and subtract 5 years from that. So, when I select a date range of November 2013 - December 2013 in timeline filter, the additional pivot chart will show the details for November 2008 - December 2013. This is where I get stuck.
I'm using two SSAS cubes which I'm combining together in PowerPivot and then display in Pivot Tables and Pivot Charts.