x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 381

# Adjust Holidays Between Two Dates

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
tracyms
• 15
• 10
3 Solutions

Older than dirtCommented:
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
``````
0

Author Commented:
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

Author Commented:
Maybe I can use the workday function but I don't think it calulates weekends.
0

Older than dirtCommented:
Sort of but where would you put those days?
0

Author Commented:
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

Author Commented:
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

Older than dirtCommented:
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

Author Commented:
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

Older than dirtCommented:
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

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
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
Exit For
End If
Next
End If
Next
Next

End Sub
``````
0

Author Commented:
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

Older than dirtCommented:
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

Author Commented:
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

Older than dirtCommented:
Sure. Run the CheckForHolidays macro
MartyholidaysRevisited.xlsm
0

Author Commented:
Same error - I have excel 2007...not sure what's happening.
0

Older than dirtCommented:
Oh heck. I see problems. I'll post again.
0

Older than dirtCommented:
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

Author Commented:
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

Older than dirtCommented:
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

Author Commented:
Thanks, I'll work on it and let you know.
0

Author Commented:
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

0

Author Commented:

"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

Author Commented:
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

Author Commented:
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.

0

Older than dirtCommented:
You're welcome and I'm glad that I was able to help.

Marty - MVP 2009 to 2012
0

Author Commented:
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.