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
LVL 49

Expert Comment

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

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 19

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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
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…

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now