timeline 1

[komark]

Hi,
I have a excel file with columns of:

Name Project Name Start_Date Finish_Date


I want to create a timeline from today (12/02/05) till 6 months from now. It will be something like this:
12/02 12/09 12/16 12/23 12/30

I want to evaluate the start date and finish date of each Person to see if it falls between my timeline. If it does than put the project name in the right column in the timeline.

Can anyone help me?

 

[fumei]

Make your date points Dates. Validate against them.

Gerry

 

[komark]

how do you do that?

 

[SkipVought]

FYI,


Why do Dates and Times seem to be so much trouble? faq68-5827

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[komark]

Skip,
I wish i knew.

 

[AnotherHiggins]

Did you read the FAQ? Do you have any questions about it?

[tt]_____
[blue]-John[/blue][/tt]

Help us help you. Please read FAQ181-2886 before posting.

 

[komark]

I read the FAQ but it doesnt solve my problem of creating a timeline

 

[SkipVought]

Sorry, if you can't understand this FAQ, I'm afraid nothing we can suggest will help you.

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[komark]

Thank you for being rude

 

[EBGreen]

You ask a question:

komark (TechnicalUser)
2 Dec 05 18:16
Hi,
I have a excel file with columns of:

Name Project Name Start_Date Finish_Date


I want to create a timeline from today (12/02/05) till 6 months from now. It will be something like this:
12/02 12/09 12/16 12/23 12/30

I want to evaluate the start date and finish date of each Person to see if it falls between my timeline. If it does than put the project name in the right column in the timeline.

Can anyone help me?

And are given an answer:

fumei (TechnicalUser)
3 Dec 05 1:21
Make your date points Dates. Validate against them.

Gerry

You didn't understand the answer and asked for clarification:

komark (TechnicalUser)
5 Dec 05 1:53
how do you do that?

Clarification was provided:

SkipVought (Programmer)
5 Dec 05 11:19


FYI,


Why do Dates and Times seem to be so much trouble? FAQ68-5827

Skip,

If you need clarification of the clarification, then there won't be a lot that can be done to help you until you develop a certain level of proficiency yourself. What Skip said might be abrupt, but it is accurate and not particularly rude especially by the standards of most web fora.

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[komark]

That FAQ does not solve my problem. I need to write a macro that would create the timeline. Changing the dates into numbers wont help me.

 

[EBGreen]

So, how much have you written so far?

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[komark]

This is what I have. I have a starting date (today) and i know that I have to stop in 6 months. Working on the loop


Sub Timeline()

Dim StartDate
Dim EndDate

' MyDate = Date$
StartDate = "=TODAY()"
EndDate = "=TODAY()+183"
Range("B2").Select
ActiveCell.FormulaR1C1 = StartDate

For x = 1 To 1000 Step 1
If StartDate <= EndDate Then


Range("D2").Select
ActiveCell.FormulaR1C1 = EndDate

End Sub

 

[AnotherHiggins]

As far as having the dates fill in, you can use

Sub Timeline()
Range("a1") = "=today()"
For i = 1 To 26
Range("a1").Offset(i) = "=R[-1]C+7"
Next i
Range("a:a").NumberFormat = "mm/dd/yy"
End Sub

If you want the dates to be static (not change every time you open the file), then add this as the last line before the End Sub:

Range("a:a").Value = Range("a:a").Value

If you want the dates to go across instead of down, then just make the following changes:

Sub Timeline()
Range("a1") = "=today()"
For i = 1 To 26
Range("a1").Offset([red],[/red] i) = "=RC[red][-1][/red]+7"
Next i
Range("[red]1:1[/red]").NumberFormat = "mm/dd/yy"
End Sub

NOTE:You were using 183 days (365days/2) to determine 6 months, I am using 26 weeks (52 weeks/2). If that's not ok, it can be changed.

[tt]_____
[blue]-John[/blue][/tt]

Help us help you. Please read FAQ181-2886 before posting.

 

[SkipVought]

Changing the dates into numbers wont help me.

As the FAQ explained, real dates are ALREADY NUMBERS. Text strings the LOOK LIKE DATES are not dates and cannot be so manipulated.

The whole point of this early exchange being, if what you have in your sheet are not REAL DATES, then that's the place to begin BEFORE any kind of macro will work.

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[komark]

John,
Thank you soo much for your help. What does this code mean:
"=RC[-1]+7"

 

[AnotherHiggins]

The R stands for Row and the C stands for Column. It is just a different kind of cell reference. The "RC[-1]" part tells excel that you want the cell that is in the same row, one column to the left. So putting "=RC[-1]+7" in B1 yields "=A1+7".

Type Cell Reference into Excel's help file (Answer Wizard tab) and click on the "R1C1 reference style" for more info.

[tt]_____
[blue]-John[/blue][/tt]

Help us help you. Please read FAQ181-2886 before posting.

 

[komark]

Thanks John for your explanation