Solved

Adjust Holidays Between Two Dates

Posted on 2013-01-30
25
320 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
  • 15
  • 10
25 Comments
 
LVL 45

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
 

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
 

Author Comment

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

Expert Comment

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

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
 

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 45

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
 

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 45

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
 

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 45

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
 

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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 45

Expert Comment

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

Author Comment

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

Expert Comment

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

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
 

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 45

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
 

Author Comment

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

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
 

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
 

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
 

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 45

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
 

Author Closing Comment

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

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

A short article about problems I had with the new location API and permissions in Marshmallow
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

762 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

16 Experts available now in Live!

Get 1:1 Help Now