Creating a calendar with horizontal bar for time duration

Posted on 2014-12-25
Medium Priority
Last Modified: 2014-12-28
First this isn't exactly a Gantt chart. Plenty of examples but not specifically what I am looking for.

Row 1 would display dates (F1(1/1/2015 for example) to never ending)

Row 3 (C3 Start date, C4 end date, and C5 Duration, which is C4-C3) The rows would also be limitless

Is there a way that the start date could find a matching date in row 1 and the intersection (cell) would be filled in then the number of duration would fill, horizontally, the remaining cells? If the duration is 3 days then 3 cells are filled in.

Thank you for any assistance
Question by:dgd1212
  • 4
  • 2
  • 2
LVL 52

Expert Comment

by:Martin Liss
ID: 40517585
Can you manually create a small sample worksheet that shows your desired result?

Author Comment

ID: 40517729
Per your request I have attached spreadsheet. I manually entered the filled in cells, which I hope to be the final result of picking dates.
Thank you for looking at this.TEST-Rolling-Calendar.xlsx
LVL 52

Expert Comment

by:Martin Liss
ID: 40517745
Would a macro that creates/updates the "bars" when the duration changes be acceptable?
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

LVL 52

Accepted Solution

Martin Liss earned 1000 total points
ID: 40517802
The attached workbook contains the following macro which is called when the duration changes.  

Sub CreateBars()
Dim lngLastRow As Long
Dim lngRow As Long
Dim lngLastCol As Long
Dim lngCol As Long

With Sheets("CALENDAR")
    Application.ScreenUpdating = False
    lngLastRow = .Range("A1048576").End(xlUp).Row
    lngLastCol = .Cells.Find("*", SearchOrder:=xlByColumns, LookIn:=xlValues, SearchDirection:=xlPrevious).Column
    ' Clear the bars (the "Split(Cells(1, lngLastCol).Address, "$")(1)" part
    ' converts the column number into a letter)
    .Range("F3:" & Split(Cells(1, lngLastCol).Address, "$")(1) & lngLastRow).Interior.TintAndShade = 0
    For lngRow = 3 To lngLastRow
        ' Check duration
        If Val(.Cells(lngRow, 5)) > 0 Then
            For lngCol = 6 To lngLastCol
                ' Check to see if the cell's date falls between the
                ' start and stop values inclusive
                If .Cells(1, lngCol) >= .Cells(lngRow, 3) And _
                   .Cells(1, lngCol) <= .Cells(lngRow, 4) Then
                   .Cells(lngRow, lngCol).Interior.Color = vbBlack
                End If
        End If
    Application.ScreenUpdating = True
End With

End Sub

Open in new window

LVL 25

Assisted Solution

by:Ejgil Hedegaard
Ejgil Hedegaard earned 1000 total points
ID: 40517989
Or use conditional formatting with this rule in F1 for the range F:XFD

Author Closing Comment

ID: 40520445
Both solutions worked. Not sure if one is more advantageous than the other but both function as needed. Many thanks for your help.
LVL 52

Expert Comment

by:Martin Liss
ID: 40520578
If you use the Conditional formatting approach I believe you'll have to update it if you add more dates, but in any case you're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2014
LVL 25

Expert Comment

by:Ejgil Hedegaard
ID: 40521040
It is not needed to update the conditional format when more dates are added in row 1, or columns C and D.
The rule is set for all rows, and all columns from F to the last column on the sheet XFD.
Also clears the cell colour when input are deleted.
The last rule F$1<>"" ensures that cells with no date in row 1, does not turn black when comparing to empty cells in columns C and D.
If you want the rule to require data in column A and B also, change to

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

600 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question