Ask Your Question

How to use VBA to make a Gantt chart that includes timestamps for day, hour, and minute?

asked 2021-05-07 11:00:00 +0000

djk gravatar image

edit retag flag offensive close merge delete

1 Answer

Sort by ยป oldest newest most voted

answered 2022-07-28 22:00:00 +0000

lalupa gravatar image

To create a Gantt chart in Excel with timestamps for day, hour, and minute, you can use VBA code. Here are the steps to create a Gantt chart with timestamps:

Step 1: Prepare your data

Your data should be in the following format:

  • Column A: Task Name
  • Column B: Start Time
  • Column C: End Time
  • Column D: Label

Columns B and C should contain timestamps in the format of "mm/dd/yyyy hh:mm:ss". Note that the format may vary depending on your system settings.

Step 2: Create a new module

In the Excel workbook, press ALT + F11 to open the Visual Basic Editor. Then, go to "Insert" > "Module" to create a new module.

Step 3: Insert the VBA code

Copy and paste the following VBA code into the module:

Sub CreateGanttChart()
    Dim cht As Chart
    Dim i As Integer

    'Create a new chart
    Set cht = ActiveSheet.Shapes.AddChart2(297, xlXYScatterLinesNoMarkers).Chart

    'Set chart title
    cht.HasTitle = True
    cht.ChartTitle.Text = "Gantt Chart"

    'Set X-axis and Y-axis labels
    cht.Axes(xlCategory).HasTitle = True
    cht.Axes(xlCategory).AxisTitle.Text = "Timeline"
    cht.Axes(xlValue).HasTitle = True
    cht.Axes(xlValue).AxisTitle.Text = "Task Name"

    'Set X-axis to display time
    cht.Axes(xlCategory).TickLabels.NumberFormat = "mm/dd/yyyy hh:mm:ss"

    'Loop through the data and create series for each task
    For i = 2 To Range("A" & Rows.Count).End(xlUp).Row
        With cht.SeriesCollection.NewSeries
            .Name = Range("A" & i).Value
            .XValues = Array(Range("B" & i).Value, Range("C" & i).Value)
            .Values = Array(i - 1, i - 1)
            .DataLabels.NumberFormat = Range("D" & i).Value
        End With
    Next i
End Sub

Step 4: Run the macro

In the Visual Basic Editor, go to "Debug" > "Compile VBAProject" to check for any errors in the code. Then, go back to the Excel workbook and press ALT + F8 to open the Macros dialog box. Select the "CreateGanttChart" macro and click "Run" to create the Gantt chart.

After running the macro, you should see a Gantt chart with timestamps for day, hour, and minute. The X-axis will display the timeline in the format of "mm/dd/yyyy hh:mm:ss". The Y-axis will display the task names. Each task will be represented as a horizontal line with a label indicating the task duration.

edit flag offensive delete link more

Your Answer

Please start posting anonymously - your entry will be published after you log in or create a new account. This space is reserved only for answers. If you would like to engage in a discussion, please instead post a comment under the question or an answer that you would like to discuss

Add Answer

Question Tools


Asked: 2021-05-07 11:00:00 +0000

Seen: 8 times

Last updated: Jul 28 '22