Workday function combined with If function help

[ttanika]

I have been working on this for a while and I can not figure it out.

G8= Student entry date
H8 = # of days in attendance
I8 = =IF(H8<=30,P8-H8,"Ineligible")
column I = the date students are eligible for 30 day release

P8 = =IF(G8>=1,G8+75,"")

column P = the date students are eligible for 75 day release

I need P8 to exclude weekends and holidays, so the 75 day release date will not include weekends and holidays. I tried to use the workday function. Can't figure it out. I have a list of the holidays on another sheet called Holidays.

 

[Rosen]

While I have a solution, it is not necessarily the best one. But here it is.

Step 1: Open the VB Editor (Alt+F11)
Step 2: Click Insert from the Menu and Select Module
Step 3: Right Click on the new Module (default Module1) and select View Code
Step 4: Copy and past the following code into the Module:

Function NumberOfExtraDays(startDate As Date, endDate As Date) As Long
    Dim i As Long, ExtraDays As Long, Holiday As Worksheet, ThisDay As Range
    Dim Holidays As Range
    For i = CLng(startDate) To CLng(endDate) Step 1
        If Weekday(i, vbSunday) = 1 Or Weekday(i, vbSaturday) = 1 Then
            ExtraDays = ExtraDays + 1
        Else
            On Error Resume Next
            Set Holiday = Sheets("Holidays")
            If Not Err.Number = 0 Then
                Err.Clear
                GoTo NextI
            End If
            Set Holidays = Holiday.Range("Holidays")
            If Not Err.Number = 0 Then
                Err.Clear
                GoTo NextI
            End If
            For Each ThisDay In Holidays
                If CLng(ThisDay.Value) = i Then
                    ExtraDays = ExtraDays + 1
                    Exit For
                End If
            Next ThisDay
        End If
NextI:
    Next i
    If ExtraDays > 0 Then
        NumberOfExtraDays = ExtraDays + NumberOfExtraDays(endDate + 1, endDate + ExtraDays)
    Else
        NumberOfExtraDays = ExtraDays
    End If
End Function

Step 5: Change the P8 formula to =IF(G8>=1,G8+75+NumberOfExtraDays(G8, G8+75),"")
Step 6: On the 'Holidays' tab highlight all your vacation days and in the Name Box type "Holidays" (without the quotes)

Note: this assumes that on the Holiday tab you have all the individual holiday dates from the earilest possible G8 date until you need them till (years in the future?).

Hope this helps!

 

[ttanika]

While I have a solution, it is not necessarily the best one. But here it is.

Step 1: Open the VB Editor (Alt+F11)
Step 2: Click Insert from the Menu and Select Module
Step 3: Right Click on the new Module (default Module1) and select View Code
Step 4: Copy and past the following code into the Module:

Function NumberOfExtraDays(startDate As Date, endDate As Date) As Long
    Dim i As Long, ExtraDays As Long, Holiday As Worksheet, ThisDay As Range
    Dim Holidays As Range
    For i = CLng(startDate) To CLng(endDate) Step 1
        If Weekday(i, vbSunday) = 1 Or Weekday(i, vbSaturday) = 1 Then
            ExtraDays = ExtraDays + 1
        Else
            On Error Resume Next
            Set Holiday = Sheets("Holidays")
            If Not Err.Number = 0 Then
                Err.Clear
                GoTo NextI
            End If
            Set Holidays = Holiday.Range("Holidays")
            If Not Err.Number = 0 Then
                Err.Clear
                GoTo NextI
            End If
            For Each ThisDay In Holidays
                If CLng(ThisDay.Value) = i Then
                    ExtraDays = ExtraDays + 1
                    Exit For
                End If
            Next ThisDay
        End If
NextI:
    Next i
    If ExtraDays > 0 Then
        NumberOfExtraDays = ExtraDays + NumberOfExtraDays(endDate + 1, endDate + ExtraDays)
    Else
        NumberOfExtraDays = ExtraDays
    End If
End Function

Step 5: Change the P8 formula to =IF(G8>=1,G8+75+NumberOfExtraDays(G8, G8+75),"")
Step 6: On the 'Holidays' tab highlight all your vacation days and in the Name Box type "Holidays" (without the quotes)

Note: this assumes that on the Holiday tab you have all the individual holiday dates from the earilest possible G8 date until you need them till (years in the future?).

Hope this helps!

Thank you for your assistance!

I did as you said, but the date i get in P8 is 5/2/13. If the entry date in G8 is 1/17/2013 then the date in P8 should be 5/13/13 if the weekends and holidays are not included and yes i do have a list of all holidays.

 

[Rosen]

When you click on the Name Box drop down menu do you have an option to select 'Holidays'?

 

[ttanika]

When you click on the Name Box drop down menu do you have an option to select 'Holidays'?

Yes, there is an option to select 'Holidays'.

 

[Rosen]

I am not sure why it is not working for you as it is working for me. Another option is to change the the line...

Set Holidays = Holiday.Range("Holidays")

to...

Set Holidays = Holiday.Range("ExplicitRange")

where ExplicitRange is replaced with the range address string assoicated to the range that contains the holidays, ie "A1:Z500".

 

[ttanika]

I am not sure why it is not working for you as it is working for me. Another option is to change the the line...

Set Holidays = Holiday.Range("Holidays")

to...

Set Holidays = Holiday.Range("ExplicitRange")

where ExplicitRange is replaced with the range address string assoicated to the range that contains the holidays, ie "A1:Z500".

I'm not sure, but i followed every step. Can I send it to you, so you can take a look?

 

[ttanika]

OK! I it worked!!! Thanks so much! Will i be able to do the same thing with the other columns? I have a column where instead of figuring out the students 75 day release I need to figure out their 30 day release date? Can i use the same code? If so, do I create another Module?

In column "I" this it the formula I have =IF(H8<=30,P8-H8,"Ineligible")

H8 = the # of days students have attended school

If we used the same entry date "1/17/2013" then the student should become eligible for 30 day release on "03/01/2013" if weekends and holidays are excluded. Holidays during this time period are (1/21/2013 and 2/18/2013)? This is also assuming that the student has been present in school for 30 school days.

 

[ttanika]

OK! I it worked!!! Thanks so much! Will i be able to do the same thing with the other columns? I have a column where instead of figuring out the students 75 day release I need to figure out their 30 day release date? Can i use the same code? If so, do I create another Module?

In column "I" this it the formula I have =IF(H8<=30,P8-H8,"Ineligible")

H8 = the # of days students have attended school

If we used the same entry date "1/17/2013" then the student should become eligible for 30 day release on "03/01/2013" if weekends and holidays are excluded. Holidays during this time period are (1/21/2013 and 2/18/2013)? This is also assuming that the student has been present in school for 30 school days.

Also, I take my previous statement back...I'm getting 5/14/2013 instead of 5/13/2013. Maybe the formula does not count 1/17/2013. I don't know.

I decided to change the days to 74 in the formula and I get 5/13/2013.

 

[Rosen]

Your formula is adding 75 days to the start day, making the end day the 76th day including the start day. If you change 75 to 74 in the code make sure your change it it both places (=IF(G8>=1,G8+74+NumberOfExtraDays(G8, G8+74),"")).

If you want a formula to determine the 30th day (assuming your start day is day one) use the same code as the 75 days but add 29 (or 30 if the start date doesn't count) instead of 74/75

=IF(G8>=1,G8+29+NumberOfExtraDays(G8, G8+29),"")