Solved

Adjust Holidays Between Two Dates

Posted on 2013-01-30
25
370 Views
Last Modified: 2013-02-08
I have a list of 2 dates, start date and end date. I also have a list of days for training between the dates. Lastly I have a list of holiday dates and their days. I'd like to check the holidays and if they're included between the start and end dates I'd like to make up those holidays by adding more dates/days to the end of end date. The thing is, I need the dates of the following days that's scheduled - not just adding extra days.

I have an attached spreadsheet that lists details of what I'm trying to do.
holidaysRevisited.xlsm
0
Comment
Question by:tracyms
[X]
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
  • 15
  • 10
25 Comments
 
LVL 48

Expert Comment

by:Martin Liss
ID: 38837802
This will add to the dates, but I don't know what you mean by  "I need the dates of the following days that's scheduled - not just adding extra days."

Sub CheckForHolidays()
Dim lngLastRow As Long
Dim lngRow As Long
Dim lngCol As Long
Dim lngDay As Long
Dim lngLastHoliday As Long
Dim intHolidayWD As Integer

lngLastRow = Range("A65536").End(xlUp).Row
lngLastHoliday = Range("I65536").End(xlUp).Row

For lngRow = 2 To lngLastRow
    For lngCol = 3 To 7
        If Cells(lngRow, lngCol).Value = "x" Then
            For lngDay = DateSerial(Mid(Cells(lngRow, 1).Value, 7), Mid(Cells(lngRow, 1).Value, 1, 2), Mid(Cells(lngRow, 1).Value, 4, 2)) To _
                         DateSerial(Mid(Cells(lngRow, 2).Value, 7), Mid(Cells(lngRow, 2).Value, 1, 2), Mid(Cells(lngRow, 2).Value, 4, 2))
                intHolidayWD = Weekday(lngDay)
                If intHolidayWD = lngCol Then
                   Cells(lngRow, 2).Value = DateAdd("d", 1, Cells(lngRow, 2).Value)
                Exit For
                End If
            Next
        End If
    Next
Next

Open in new window

0
 
LVL 1

Author Comment

by:tracyms
ID: 38837968
I mean that if the training is on Mon and Wed of each week and the holiday falls on one those days, I want to make up those days but have them fall on the same day (Mon/Wed). Does that make sense? Maybe I'm making it too complicated...
0
 
LVL 1

Author Comment

by:tracyms
ID: 38838002
Maybe I can use the workday function but I don't think it calulates weekends.
0
Technology Partners: 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!

 
LVL 48

Expert Comment

by:Martin Liss
ID: 38838018
Sort of but where would you put those days?
0
 
LVL 1

Author Comment

by:tracyms
ID: 38838022
One more time:

- Start_Date = 1/12/13  
- End_Date  = 1/30/13
- Days_Per_Week_of_Training = Mon and Wed
- Oops_Holiday = 1/30/13 (say it falls on a Wed)
- Need extra day = Mon (since training is Mon and Wed)
If the holiday fell on a Mon, the end date would be that Wed.

If there were 2 holidays that fell on mon or wed, then increase end dates twice but should be the following mon and wed.
0
 
LVL 1

Author Comment

by:tracyms
ID: 38838026
The extra days could go in a cell next to each end date (preferred so I can make sure it's working) but it's meant to replace the end date.
0
 
LVL 48

Expert Comment

by:Martin Liss
ID: 38838029
Okay, after I add one to the date in column B for the Monday holiday I assume you want me to keep on adding until column B is a Monday. Then for the Wednesday holiday I add more days until column B is a wednesday. Correct?
0
 
LVL 1

Author Comment

by:tracyms
ID: 38838058
I've attached an example using the workday function - just messing around with it until it gave me the right date. It adds an extra day (new end date). Let me know if makes sense what I'm trying to accomplish. Thanks!
holidaysRevisited.xlsm
0
 
LVL 48

Assisted Solution

by:Martin Liss
Martin Liss earned 500 total points
ID: 38838060
Try this
Sub CheckForHolidays()
Dim lngLastRow As Long
Dim lngRow As Long
Dim lngCol As Long
Dim lngDay As Long
Dim lngLastHoliday As Long
Dim intHolidayWD As Integer
Dim intDaysAdded

lngLastRow = Range("A65536").End(xlUp).Row
lngLastHoliday = Range("I65536").End(xlUp).Row

For lngRow = 2 To lngLastRow
    For lngCol = 3 To 7
        If Cells(lngRow, lngCol).Value = "x" Then
            For lngDay = DateSerial(Mid(Cells(lngRow, 1).Value, 7), Mid(Cells(lngRow, 1).Value, 1, 2), Mid(Cells(lngRow, 1).Value, 4, 2)) To _
                         DateSerial(Mid(Cells(lngRow, 2).Value, 7), Mid(Cells(lngRow, 2).Value, 1, 2), Mid(Cells(lngRow, 2).Value, 4, 2))
                intHolidayWD = Weekday(lngDay)
                If intHolidayWD = lngCol Then
                    Do Until Weekday(Cells(lngRow, 2).Value) = lngCol
                        intDaysAdded = intDaysAdded + 1
                        Cells(lngRow, 2).Value = DateAdd("d", 1, Cells(lngRow, 2).Value)
                    Loop
                    MsgBox "Found that " & DateSerial(Mid(Cells(lngRow, 1).Value, 7), Mid(Cells(lngRow, 1).Value, 1, 2), Mid(Cells(lngRow, 1).Value, 4, 2)) _
                         & " is a " & Cells(1, intHolidayWD) & " so I added " & intDaysAdded & " days to make it " & Cells(lngRow, 2).Value
                    intDaysAdded = 0
                    Exit For
                End If
            Next
        End If
    Next
Next

End Sub

Open in new window

0
 
LVL 1

Author Comment

by:tracyms
ID: 38838069
I'm getting a runtime error 13  type mismatch

I click debug and below was highlighted:

            For lngDay = DateSerial(Mid(Cells(lngRow, 1).Value, 7), Mid(Cells(lngRow, 1).Value, 1, 2), Mid(Cells(lngRow, 1).Value, 4, 2)) To _
                         DateSerial(Mid(Cells(lngRow, 2).Value, 7), Mid(Cells(lngRow, 2).Value, 1, 2), Mid(Cells(lngRow, 2).Value, 4, 2))
                intHolidayWD = Weekday(lngDay)
0
 
LVL 48

Expert Comment

by:Martin Liss
ID: 38838073
Are you using the original workbook or the one that you modified? In any case maybe I should  fade away into the background since you seem to have what you want.
0
 
LVL 1

Author Comment

by:tracyms
ID: 38838079
No I do not, I was simply giving you an example - I have no idea how to get the workday function to do what I need each time. I was hoping you could shed some light in case that was an option. I did try the original spreadsheet. If you have it working, can you attach what you have?
0
 
LVL 48

Expert Comment

by:Martin Liss
ID: 38838088
Sure. Run the CheckForHolidays macro
MartyholidaysRevisited.xlsm
0
 
LVL 1

Author Comment

by:tracyms
ID: 38838092
Same error - I have excel 2007...not sure what's happening.
0
 
LVL 48

Expert Comment

by:Martin Liss
ID: 38838093
Oh heck. I see problems. I'll post again.
0
 
LVL 48

Expert Comment

by:Martin Liss
ID: 38838345
Here's a workbook saved in Excel 97 to 2003 format.If you can open it and run the code without changing the data on Sheet1 then you'll get two messages. Let me know if they are correct.
MartyholidaysRevisited.xls
0
 
LVL 1

Author Comment

by:tracyms
ID: 38839411
Same error on this line:

lngHolidaySerial = DateSerial(Mid(Cells(lngHoliday, 9).Value, 7), Mid(Cells(lngHoliday, 9).Value, 1, 2), Mid(Cells(lngHoliday, 9).Value, 4, 2))
0
 
LVL 48

Expert Comment

by:Martin Liss
ID: 38839686
I'm sorry but I don't have Excel2007 so I can't debug this for you.

You can debug it yourself by first changing how lngDay is defined. Just do Dim lngDay As Variant and see what happens.

If that doesn't help then when it gets to that line try to determine what part is causing the problem. You can do that by highlighting such things as Cells(lngHoliday, 9).Value and clicking the Quick Watch (eyeglasses) button which should be the holiday date, Mid(Cells(lngHoliday, 9).Value, 7) which should be the 2 digit year, Mid(Cells(lngHoliday, 9).Value, 1, 2) which should be the 2 digit month, etc.

I wrote this tutorial on debugging that may help you. Don't worry that it says VB6. Most of it applies to VBA.
0
 
LVL 1

Author Comment

by:tracyms
ID: 38839740
Thanks, I'll work on it and let you know.
0
 
LVL 1

Author Comment

by:tracyms
ID: 38847455
I can't get your above code to work. Can we try this:

=SUMPRODUCT((Holidays>=F11)*(Holidays<=H11))

The above excel formula gives me a count of how many holidays are between the date range. How can I find out what days (M, T, W, TH, F, S, SU) they are? At least I use this information to see if the date falls on a day of the training.



The link below describes what I'd like to do but I just can't get there:

http://www.pcreview.co.uk/forums/formula-determine-whether-date-falls-between-two-dates-t3739337.html

Thanks again for any help you can provide.
0
 
LVL 1

Author Comment

by:tracyms
ID: 38847505
In the above link:

"Ultimately I will need to know which of the holidays falls within the
Start/End dates on any given line because I will need to know what day of the
week (mon, tue, etc., --which are listed in V3:V9 next to the holidays using
a TEXT formula) and then use this information to subtract those days from a
total number of sessions/hours which have been calculated in different
columns."

I actually want to have the number of sessions subtracted to go to a separate cell so I can adjust the end date. For example if 2 holidays fall between the start and end date, AND if they fall on a day or days of the week of the holiday, subtract those dates from the session and send it to a separate cell. I can then see how many more sessions I need to add and use that information to increase the end date.
0
 
LVL 1

Author Comment

by:tracyms
ID: 38847577
Start Date = A2

End Date = B2

Days (C2:I2) - week columns with Mon starting in C2 and Sunday ending in I2
These day rows have a drop down to select the days (Mon drop down has "M", Tue drop down has "T", etc).

Total number of each Mon, Tue, etc are counted (J2:P2) using formula =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A2&":"&B2)))=2))

Sessions = forumula =IF(Mon cell ="M",J2,0)

Holiday Dates (R2:R10)

Holiday Days (S2:S10) - Using formula =TEXT(Holiday Dates, "ddd")
0
 
LVL 1

Assisted Solution

by:tracyms
tracyms earned 0 total points
ID: 38848014
I think I figured it out. I used the weekday function to get the number (serial) for the holiday days of the week and compared it between the start and end dates, then counted them - that got me the number of holidays that fall on each weekday (2 is the serial number for Tuesday):

=COUNTIFS(holiday serial,"2",holiday dates,">="start date,holiday dates,"<=end date)

I then compared the number of sessions per week to the number of holidays per week, and summed the value in the number of holildays if the session per week was greater than or equal to zero.  

=SUMIFS(Mon,Mon,">0",Number of Mon Session,">0")+SUMIFS(Tue,Tue,">0",Number of Tue Session,">0")...etc, through Sunday.

Thanks MartinLiss for your help.
0
 
LVL 48

Accepted Solution

by:
Martin Liss earned 500 total points
ID: 38848525
You're welcome and I'm glad that I was able to help.

Marty - MVP 2009 to 2012
0
 
LVL 1

Author Closing Comment

by:tracyms
ID: 38867410
Figured it out using Google.
0

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.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Starting up a Project

624 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