Solved

# Adjust Holidays Between Two Dates

Posted on 2013-01-30
364 Views
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
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
• 15
• 10

LVL 47

Expert Comment

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
``````
0

LVL 1

Author Comment

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

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

LVL 47

Expert Comment

ID: 38838018
Sort of but where would you put those days?
0

LVL 1

Author Comment

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

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 47

Expert Comment

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

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 47

Assisted Solution

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

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

LVL 1

Author Comment

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 47

Expert Comment

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

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 47

Expert Comment

ID: 38838088
Sure. Run the CheckForHolidays macro
MartyholidaysRevisited.xlsm
0

LVL 1

Author Comment

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

LVL 47

Expert Comment

ID: 38838093
Oh heck. I see problems. I'll post again.
0

LVL 47

Expert Comment

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

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 47

Expert Comment

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

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

LVL 1

Author Comment

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

0

LVL 1

Author Comment

ID: 38847505

"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

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

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.

0

LVL 47

Accepted Solution

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

ID: 38867410
0

## Featured Post

Question has a verified solution.

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

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Starting up a Project
###### Suggested Courses
Course of the Month4 days, 23 hours left to enroll