Solved

Holidays Between 2 Dates

Posted on 2013-01-29
7
297 Views
Last Modified: 2013-01-30
I found a bit of vb code that’s close to what I need here:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_23195737.html

I’ve listed it below but I’d like change it so that instead of returning a list dates in between the start and end dates, I want to return the list of holidays between the start and ends dates. I have a list of holiday dates in Row Y.

Private Sub CommandButton1_Click()

    startdate = Range("F11").Value
    enddate = Range("G11").Value
    daysbetween = enddate - startdate
    Results = "Day 1" & vbTab & startdate & vbCrLf
    For i = 1 To daysbetween
        nextdate = startdate + i
        Results = Results & "Day " & i & vbTab & nextdate & vbCrLf
    Next i
    MsgBox Results
End Sub

Open in new window

0
Comment
Question by:tracyms
  • 4
  • 3
7 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 38833911
Try this loop in your code. I have not tested it. If you have a problem then post your file and I can try to solve it.


for each cel in range("y1:y"& range("y" & rows.count).end(xlup).row)
if cel.value >= startdate and cel.value <= enddate then
 Results = Results & "Day " & cel.value - startdate & vbTab & nextdate & vbCrLf
endif
next cel
0
 

Author Comment

by:tracyms
ID: 38833927
I moved my holidays to row ak, starting in row ak2. I used the code below but it only shows the start date:


Private Sub CommandButton1_Click()

    startdate = Range("F11").Value
    enddate = Range("G11").Value
    daysbetween = enddate - startdate
    Results = "Day 1" & vbTab & startdate & vbCrLf
   
   
    For Each cel In Range("ak2:ak" & Range("ak" & Rows.Count).End(xlUp).Row)
If cel.Value >= startdate And cel.Value <= enddate Then
 Results = Results & "Day " & cel.Value - startdate & vbTab & nextdate & vbCrLf
End If
Next cel
   
   
   
    MsgBox Results
End Sub
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 38834229
Can you upload a small sample excel file for testing?
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:tracyms
ID: 38834790
I've attached a test file.
holidays.xlsm
0
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 500 total points
ID: 38834849
Put your cursor on one of the start dates or end dates and then run this macro

Private Sub CommandButton1_Click()

    startdate = ActiveCell.EntireRow.Cells(1).Value
    enddate = ActiveCell.EntireRow.Cells(2).Value
   
    For Each cel In Range("i2:i" & Range("i" & Rows.Count).End(xlUp).Row)
        If cel.Value >= startdate And cel.Value <= enddate Then
            Results = Results & "Day " & cel.Value - startdate & vbTab & cel & vbCrLf
        End If
    Next cel
   
    MsgBox Results
End Sub
0
 

Author Comment

by:tracyms
ID: 38836870
ssaqibh,

I've changed my request - if that's okay. Please see attached spreadsheet for details.
holidaysRevisited.xlsm
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 38836916
This new request is significantly differently from the original request.

If the original request is appropriately answered the you should close this question and open another with the new request.

If there is something lacking in the solution with respect to the original request then continue to post here.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
excel pivot question 4 40
change VBA to show result in cells rather than debug.print 4 34
Compile Error 7 41
MS Excel IF AND OR statement 3 26
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
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…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

912 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

21 Experts available now in Live!

Get 1:1 Help Now