Excel Time Line 2

[toddl]

Hi, I am trying to create a time line in excel with hire dates of people. On the Chart, I would also like the persons name to show up next to where there hire date is. Is this possible in excel?

I want it to look similar to this

SMITH 7/1/04 ROGERS 8/15/04
--------------*-------------*---------------

Thanks for your help,

Todd

 

[SkipVought]

Todd,

What happens when Smith is hired on 7/1/2004 and Rogers on 7/2/2004?

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[SkipVought]

What TYPE of Chart?

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[toddl]

If the hire dates were close together, would excel place them on opposite sides, such as
Smith 7/1/2004
-----*----*-----
Rogers 7/2/2004

Or it could leave the date out, and have the date be on the bottom of the chart.

As for what type of chart, I really have no idea which chart would make the best time line.

Todd

 

[SkipVought]

Todd,

1) I Named each range Date and Name using Insert/Name/Create = create name in top row.

This table is in columns A & B

2) in D1
[tt]
D1:=MIN(Date)
E1:=D1+1
[/tt]
and format as a date and COPU the formula in E1 across as far as you need.

3) in D2
[tt]
D2:=IF(ISERROR(MATCH(D$1,Date,0)),"",INDEX(Name,MATCH(D$1,Date,0),1))
[/tt]
and COPY across

VOLA!


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[toddl]

Hi Skip,


Thanks for that tip, I was able to get it to work, but it is not exactly what I was looking for.

Is there a way to put it in an actual chart, that only includes the days that have a hire date?

I want to put 2-3 years to dates on the time line, and excel will not allow that many columns. There is only going to be around 75 days that are actually used. This way it would only show the day that say smith is hired, and then no more days are shown until Rogers is hired.

Also, thanks for showing me those functions, I had never used them before.

Todd

 

[SkipVought]

OK here's another suggestion.

Create a NEW column of data called VAL with each row value of 1.

Use the Chart Wizard to create a scatter chart with Date and VAL column values.

Name the columns Name, Date, Val and use Insert/Name/Create - create name in top row to name the ranges of data.

Run this code on the sheet with the chart

Sub FormatTimeline()
   i = 1
   j = 10
   For Each ser In ActiveSheet.ChartObjects(1).Chart.SeriesCollection
      For Each dp In ser.Points
         With dp
            .HasDataLabel = True
            With .DataLabel
               .Text = Range("Name")(i, 1).Value & " " & Application.Text(Range("Date")(i, 1).Value, "mm/dd/yy")
               .Top = .Top + j
            End With
            j = j * -1
         End With
         i = i + 1
      Next
   Next
End Sub

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[toddl]

Thank you Skip,

That was exactly what I wanted.

One last question, is there a way so that the bottom will only do the first of the month, it is set at 10 days right now?

Thanks for all of your help,

Todd

 

[SkipVought]

Click on the Axis and then Format.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244