Assign Month to the Date Range (via VBA)

Posted on 2016-10-20
Medium Priority
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 53

Expert Comment

by:Ryan Chong
ID: 41853531
can you provide a sample with your existing data and expected result?
LVL 22
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 22

Accepted Solution

crystal (strive4peace) - Microsoft MVP, Access earned 2000 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

Independent Software Vendors: 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

After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

649 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