Assign Month to the Date Range (via VBA)

Posted on 2016-10-20
Last Modified: 2016-10-24
I have Excel tab with 8 dates on it (in 8 columns).
Say, in columns 13 thru 20.
Between Date from Column 13 and the Date from Column 14 there is a RangeA.
Meaning that for example Column 13 value is 1/1/2016 and Column 14 value is 4/15/2016.
So date 1/1/2016-4/15/2016 is my RangeA.
On the other Tab I have 20 columns with Months, including those above: Jan 2016 and April 2016.
I need to tie them together, meaning:
If there is RangeA in that first Tab, then on my other Tab I need to Populate that corresponding Cell(s). In our case cells Jan 2016, Feb 2016, March 2016 and April 2016 should be populated with "RangeA".
Question by:CABRLU63
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 52

Expert Comment

by:Ryan Chong
ID: 41853531
can you provide a sample with your existing data and expected result?
LVL 21
ID: 41853661
Public Function GetTheDateRange( _
   nCol1_1 As Long _
   , nCol1_2 As Long _
   , nRow1 As Long _
   , nRow2 As Long _
   ) As String
   '  nCol1_1 = column with first date to look through (RangeSheet )
   '  nCol1_1 = column with second date to look through (RangeSheet )
   '  nRow1 = row with dates to look through (RangeSheet )
   '  nRow2 = row with date on sheet with formula to look at (sheet with formula)

   '  =GetTheDateRange(13,20,1,1) --> 1/1/2016 - 4/15/2016
      Dim ws1 As Worksheet
      Dim ws2 As Worksheet

   Set ws1 = Worksheets("RangeSheet") 'RangeSheet = sheet name with dates to look through -- CUSTOMIZE
   Set ws2 = Application.Caller.Parent 'worksheet that the formula is on

   Dim nDate1 As Date _
      , nDate2 As Date _
      , nCol As Long _
      , nDate As Date _

   'initialize return value
   GetTheDateRange = ""
   nDate = CDate("1 " & ws2.Cells(nRow2, Application.Caller.Column))
   With ws1
      For nCol = nCol1_1 To nCol1_2
         If .Cells(nRow1, nCol) <= nDate Then
            If .Cells(nRow1, nCol + 1) >= nDate Then
               GetTheDateRange = "'" & .Cells(nRow1, nCol) & " - " & .Cells(nRow1, nCol + 1)
               Exit Function
            End If
         End If
      Next nCol
   End With
   On Error Resume Next
   Set ws1 = Nothing
   Set ws2 = Nothing
   Exit Function
   MsgBox Err.Description, , _
        "ERROR " & Err.Number _
        & "   GetTheDateRange "

   Resume Proc_Exit
End Function

Open in new window

LVL 18

Expert Comment

ID: 41853669
You can create a 3rd sheet and concatenate the two or if it simpler than that meaning that you have a sheet with your range A with dates like 15/1/2016 and 4/15/2016 and then you want on a second sheet to show just the month and the year, you can use a reference to the initial sheet and format the date (like the example I have attached)
LVL 21

Accepted Solution

crystal (strive4peace) - Microsoft MVP, Access earned 500 total points
ID: 41853687
I actually didn't use nDate1 or nDate2 so they can be removed from the DIM statements. Tried to edit but xtermie was too fast ;)

also changed function result to:
               GetTheDateRange = CStr(.Cells(nRow1, nCol)) & "-" _
                  & CStr(.Cells(nRow1, nCol + 1))

here is an example of what the output looks like:
Excel - screenshot showing GetDateRange function(my dates aren't in order like yours probably are)

Like Ryan, I didn't really understand what you were after so I took a guess

Author Comment

ID: 41853864
The question I've asked will help me to get started with the Gantt Chart project I'm working on.
It has to be a VBA code.
I'll have a tab with RawData (the attached ActiveProjects file) and that data will feed the the Gantt Chart (as the one on GanttCapture file).
I've already built some code to take data from the external file and populate first 4 columns with project details. Now need to start actually populating Gantt.
This is also my objective here:
The code will need to define Years (consecutive), Quarters (4 for each Year) and Months (3 for each Quarter).
Months of the Quarters (for example 1Q - 1QJan, 1QFeb, 1QMarch).
It will also have to define when the Start and End of each Quarter is. Also, tie each Quarter to the Year.
Date Ranges for Stages. For example: GREEN Stage – that is DesignStart to DesingEnd date range. YELLOW Stage – is DesingEnd to ConstructionNTP. ORANGE Stage – is from ConstructionNTP to FinalCompletion. And also BLUE Stage – is from FinalCompletion Date plus 11 Months forward.
Stages (GREEN, YELLOW, ORANGE and BLUE) defined and tied to Dates from the RawData tab (example: DesignStart_To_DesignEnd Date is GREEN) and then these Stages tied to the above Quarters.

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

724 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