Project Timeline With Dynamic Date Range?

Oct 26, 2012

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?

View 1 Replies


Excel Function Required To Allocate Project Cost Between Two Date Range

Dec 9, 2013

Attached is a spreadsheet wherein I'm trying to extrapolate project costs across various months based on working days in a month subject to start and end dates of the project. Need an accurate formula to spread the cost.

Days & Cost Allocation Example.xlsx‎

View 2 Replies View Related

Date And Time Values To Be Represented On Timeline Automatically

Jun 19, 2014

I 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.


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.

View 5 Replies View Related

Creating Named Range Taking Avg To Date Of Dynamic Range

Jul 15, 2014

I have a column of data that keeps getting new information in it. what i need is a named range that i can use for a chart, first point in the named range will be the first value in the column, second point will be avg. of point 1 and 2, then 3rd point will be avg. of 1,2,3 etc.

View 1 Replies View Related

Dynamic Range Based On Date?

Jan 30, 2013

I have data in a spreadsheet similar to the below.

[Code] ....

What I need to do is created a named dynamic range based on the current month which will contain all rows/columns within that range. Ideally I need it to create the range for the current month so I can then use this as the data source for a pivot table.

I believe it is something to do with index and match functions?

View 1 Replies View Related

Row Number Of Last Date / Time In Column (Dynamic Range)

Feb 4, 2014

I have a long (~42,000) list of dates and times (some including zeros). I would like to name these ranges and use a formula to find the last entry in each column. I know the lists will never go over 45,000.

The data is in this format:



The data will not always be consecutive, so I imagine the criteria would be something like (if there were such a thing) "ISDATE" or something of the sort.

How would I write a formula to dynamically calculate the row number of the last date or time in the column? (In this case, it would be 6.)

View 9 Replies View Related

2-dimensional Date Range With Dynamic Copy And Paste

May 23, 2007

Some of you may have seen my 2-dimensional Date Range Criteria thread . By request, that thread has been closed and I am opening a new one on a related, but infinitely more complex note.

The solution to the previous thread worked, but not well with my project. I'll go a little more in depth into how my project works, and try not to be too detailed and irritating.

I am trying to create a homebrew Learning Management System for some of the Training courses that my department offers. I've posted here a few times on the same project, and the advice from all previous posts has been excellent.

What I have so far allows users to input all of the relevant information about a participant, and then keep track of the dates when they took a particular course. They input this information with a Userform. This data goes into a Participant Master List, which is where the majority of the functions take place. There is a Workings sheet set to xlVeryHidden, which houses some other information, including results for searches, and finally there is the Report Template--the source of my original question.

The original question was how to make a formula that would track how many people from each department and site have attended training within a date range. That question was answered in the 2-dimensional Date Range Criteria of the report.

What makes it potentially more complicated is that a user might want to generate two or three reports at the same time to compare side-by-side. I'm pretty sure I can make something that will do this, and allow it, but the way it's looking could be really complicated--extreme headache, and a lot of VBA coding.

View 9 Replies View Related

Dynamic Named Range Based On Last Date In Column

Mar 9, 2008

I want to define a dynamic named range based on the last date in a range (AE4 down). Unfortunately there are gaps in this range where no date is in a cell.

View 5 Replies View Related

Excel 2013 :: Filter Date Using Timeline Filter

Dec 23, 2013

I 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.

View 1 Replies View Related

Generating List Of Project Parts Based On Project Duration And Time Window

Aug 4, 2014

I have a table of projects with 1) duration in year, 2) time window (number of years of our planning cycle), and 3) start year of the project. I want to generate a list of project parts of all projects where they may take place. This will serve as an input to an optimization program.

So a project of 2-year duration should have 2 parts over any year within the time window. I am including the "impossible" ones for my developer to tag them as "0" when we run it through his code.

View 5 Replies View Related

Consolidate 4 Excel Project Lists (Workbooks) To New Master Project List Using VBA

Sep 5, 2013

My task is to consolidate 4 Excel Project Lists (Workbooks) to a Master Workbook. The Project Lists has a different structure and almost different content. The relevant information is always on Sheet1 but it has completely different ranges. The only constant is the Project Number, which should be used to sort the information. Every Project should be listed only once with all the existing information.

I found a code written by Ron de Bruin which has already some components that I want to have in my VBA but I think there are still a lot of necessary adjustments to do.

Sub MergeSelectedWorkbooks()
Dim SummarySheet As Worksheet
Dim FolderPath As String
Dim SelectedFiles() As Variant
Dim NRow As Long
Dim FileName As String
Dim NFile As Long
Dim WorkBk As Workbook


The Master Project List should has the headers in Row1 and the information listed below. The Macro should automatically places the correct information to the correct column. Some of the information are in 2 or more of the lists but they should be listed only once in the Master List.

Project Number

Project Description


I guess a problem is that the structures of the Lists are quite different so there must be a kind of sorting process.

In the end I want to have an Excel File with the Macro and a Command Button and by clicking the Macro creates a new Workbook with the Master List.

It would be better if there is a variable range instead of a defined. Like the Macro searches the last row and starts at this row and column.

View 4 Replies View Related

Get Excel To Open MS Project File And Then Run Project Macro On It

Jul 30, 2013

I am trying to get excel to open a MS Project file and then run a project macro on it.

What is happen is that when I run my code it works, and then excels VBA window freezes up and the run arrow goes away, and if I try to open project I just get a chiming noise and it won't open.

For now I have to select the MS Project file from a directory, but in the future I would like it to go through the folder and open all of the file in the folder

Sub ImportMSProject()
Dim FileToOpen
Dim mpApp As MSProject.Application
Dim prjmacro As Object
'Identify the File to Open - START
FileToOpen = Application.GetOpenFilename("Microsoft Project Files (*.mpp), *.mpp")
If FileToOpen = False Then
Exit Sub
End If
'Identify the File to Open - END


View 9 Replies View Related

How To Forecast Project Completion Date

Sep 28, 2008

I have project start date in cell C2((MMDDYYYY format).In cell D2 I have put the total days needed to complete the project.In cell E2:E6 I have got the scheduled Holidays.

I need to calculate the project completion date in F2.We work from Monday to Saturday,Sunday being off day.

The detail is as follows:-

View 9 Replies View Related

Protect VBA Project Or Lock Project For Viewing By VBA

Apr 27, 2007

I have tried to record macro to protect VBAProject / lock project for viewing. But can not success by that way.

View 9 Replies View Related

Tracking Date Of Last Phase Change In Project Steps

May 19, 2014

I have a project with over 600 steps which we are trying to track when each part progresses by having 52 weekly columns. Their are 8 users which we have allocated a colour so when they complete one of the steps they select which step they have completed and which colour they are. This works well but I would like to put the date that the change was made in a separate cell in that row.

My question is can this be done with a formula as I am in favour of doing this as soon as someone changes a cell in the week range. If not I will need to nut through a VBA sub routine.

I know that this formaul does not work but it is along the lines of what I am testing for: =if(sum(T55:BM55)>" ",Date(),0) where if any of the cells in the range T55 to BM55 have any value or letter in them then put todays date in cell L55 else do nothing.

View 1 Replies View Related

How To Name A Dynamic Range & Make A Validation List (of 2 Dynamic Ranges)

Dec 22, 2009

I have a range which will change in size & in content, & I want this to be a Named Range at whatever size it is.

Reason I want to is because I want to make a Validation List with this dynamic range. I also want a Validation list which lists the content of 2 or more dynamic ranges which may or may not be on the same worksheet - is this possible?

First dynamic range: called "Milestones" at A11
Second dynamic range: called "Activities" at A25
& make a Validation list that will list content of both

View 9 Replies View Related

Date Function Returns Compile Error- Cant Find Project Or Library

Oct 1, 2006

I have the following

Private Sub Workbook_Open()
Worksheets("Sheet1"). Range("L5").Value = Date
End Sub

When i try and run the code it returns an error saying: "Compile Error, Cant Find Project Or Library" and it highlights the word "Date"

View 7 Replies View Related

Tally Report Of X: Count On Column B (project $) Depending On The Criteria On Column A (project Description)

Aug 28, 2007

I need to write a couple of functions. Firstly I want to do a count on column B (project $) depending on the criteria on column A (project description). For example a total count of all values in col B if project description is "P" or "A". [I can do sumif functions but this is a countif and I cannot get this right].

View 4 Replies View Related

Date Cells In Project Plan To Change Based On Other Cells Including Other Date Cells

Oct 31, 2008

This is a project plan with tasks and dates. Column A is the activity number. (Example 1, 2, 3" etc). Column B is the task (Ex. "Complete Report"). Column C is number of days required to complete the task. Column D is the dependency column. (Ex. Cell D2 =1 in other words Task 2 is dependent on task 1). Column E is the date.

I would like to have a seperate start date cell and a go live date cell.

The objective is to enter a start date, and have each column E date increase based on the number of days entered in Column C. If a task is dependent on another and I change the number of days in Column C I need the dependent task to change the same amount of days.

View 9 Replies View Related

TimeLine VBA

Aug 22, 2009

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...

View 9 Replies View Related

Formula Which Indicate Or Pop Up Timeline

Feb 18, 2008

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)

View 9 Replies View Related

Timeline With Recurring Events?

Jul 2, 2014

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.



View 1 Replies View Related

Inserting Timeline In Sheets

Jun 8, 2009

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

View 9 Replies View Related

Stop Gaps In Timeline

Jul 17, 2007

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 Related

Excel Chart To Show Timeline?

Nov 23, 2011

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?

View 3 Replies View Related

Create Gantt Or Timeline Chart

Jul 1, 2008

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.

View 9 Replies View Related

Timeline - Dates To Determine Size Of Shapes

Sep 11, 2006

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.

View 9 Replies View Related

Excel 2010 :: Generating Custom Timeline On Separate Tab?

Jan 1, 2014

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 Related

Excel 2007 :: Gantt Or Stacked Bar Chart Timeline

Mar 19, 2012

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

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...

View 3 Replies View Related

Change Bar Graph Horizontal Timeline From Ascending To Descending?

Jun 11, 2014

how can I change a bar graph horizontal time line from ascending to descending?

View 1 Replies View Related

Copyrights 2005-15, All rights reserved