Date Field - Week Dates Only

[Blackwidow]

Is there anyway for a date field to only look at Week Days ignoring weekends and Bank Holidays?

I work in a school and I have a field for todays date which is automatic and then I have a field for Number of Days where I put a Number in the third field is a due back date field which is calculated from [Totals Date]+[Number of Days] however it counts weekends..which I dont want it to do

i.e.

TODAYS DATE Friday 02/05/03
Number of Days 2
Due Back Sunday 04/05/03

I want it to ignore Sat/Sunday and return Tuesday 06/05/03


Is this possible?

 

[ColinEssex]

I want it to ignore Sat/Sunday and return Tuesday 06/05/03

What happened to Monday then? Do you want 3 day weekends?

Have a look at this thread

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=43146&highlight=weekdays+only

Courtesy of Mile-O

Col

ps - you can search this forum for "weekdays only" and see what you get

 

[Blackwidow]

3 day weekend

I would love to have three day weekends.. I was being a dope but not too much of one cause Monday is a bank Holiday so I wouldnt want to count that either...

 

[Mile-O]

I've got a function I wrote that calculates Bank Holidays for any year within a range - I'll dig it out.

 

[Mile-O]

Here it is - you can adapt it to write a function to calculate days that excludes your bank holidays.

 

[Blackwidow]

Head is now Mush

right gonna explain again.....

Okay this database is for pupils who have been excluded..
on my Form I have the following fields

Date Excluded
Number Of Days
Date Due Back

The Date Excluded is set to todays date cause they are usually excluded the day the record is created.
So all the inputter needs to put in is the Number of Days..

The Date Due Back field = [Date Excluded]+[Number Of Days] but not to include sat/Sun/BankHols...

i.e.
Date Excluded 07/05/03 'Wednesday
Number Of Days 3
Due Back 13/05/03

 

[Blackwidow]

Here it is - you can adapt it to write a function to calculate days that excludes your bank holidays.

Gonna Cry without Some help

 

[Mile-O]

Copy all of this into a module:

Public Function CountWeeks(ByVal dteStartDate As Date, intDays As Integer)
 
    On Error Goto Err_CountWeeks
    
    Dim dteTemp As Date
    
    dteTemp = dteStartDate
    
    Do While intDays <> 0
        
        Select Case WeekDay(dteTemp)
            Case Is = 1, 7
                ' do nothing
            Case Else
                Select Case dteTemp
                    Case Is = DateSerial(Year(dteTemp), 1, 1), _
                        [b]DateSerial(Year(dteTemp), 1, 2), _[/b]
                        DateOfEaster(Year(dteTemp)) - 2, _
                        DateOfEaster(Year(dteTemp)) + 1, _
                        GetBankHoliday(DateSerial(Year(dteTemp), 5, 1)), _
                        GetBankHoliday(DateSerial(Year(dteTemp), 5, 25)), _
                        GetBankHoliday(DateSerial(Year(dteTemp), 8, 25)), _
                        DateSerial(Year(dteTemp), 12, 25), _
                        DateSerial(Year(dteTemp), 12, 26)
                        ' do nothing
                    Case Else
                        intDays = intDays - 1
                End Select
        End Select
        dteTemp = dteTemp + 1
    Loop
    
    CountWeeks = dteTemp - 1 ' remove 1 due to final of loop

Exit_CountWeeks:
    Exit Function

Err_CountWeeks:
    MsgBox Err.Description, vbExclamation, "Error #" & Err.Number
    Resume Exit_CountWeeks
        
End Function

Public Function DateOfEaster(ByVal intYear As Integer) As Date

    On Error GoTo Err_DateOfEaster
 
    Dim intDominical As Integer, intEpact As Integer, intQuote As Integer
    
    intDominical = 225 - (11 * (intYear Mod 19))
    
    ' if the Dominical is greater than 50 then subtract multiples of 30 until the resulting
    ' new value of it is less than 51
    If intDominical > 50 Then
        While intDominical > 50
            intDominical = intDominical - 30
        Wend
    End If
    
    ' if the Dominical is greater than 48 subtract 1 from it
    If intDominical > 48 Then intDominical = intDominical - 1
    
    intEpact = (intYear + Int(intYear / 4) + intDominical + 1) Mod 7
    
    intQuote = intDominical + 7 - intEpact
    
    ' if the quote is less than 32 then Easter is in March
    ' if the quote is greater than 31 then the quote minus 31 is its date in April
    If intQuote > 31 Then
        DateOfEaster = DateSerial(intYear, 4, intQuote - 31)
    Else
        DateOfEaster = DateSerial(intYear, 3, intQuote)
    End If

Exit_DateOfEaster:
    Exit Function

Err_DateOfEaster:
    MsgBox Err.Description, vbExclamation, "Error #" & Err.Number
    Resume Exit_DateOfEaster

End Function

Public Function GetBankHoliday(ByRef dteBankHoliday As Date) As Date

    On Error Goto Err_GetBankHoliday

   Dim intCounter As Integer
    For intCounter = 0 To 6
        If WeekDay(dteBankHoliday + intCounter) = 2 Then
            dteBankHoliday = dteBankHoliday + intCounter
            Exit For
        End If
    Next intCounter
    GetBankHoliday = dteBankHoliday

Exit_GetBankHoliday:
    Exit Function

Err_GetBankHoliday:
    MsgBox Err.Description, vbExclamation, "Error #" & Err.Number
    Resume Exit_GetBankHoliday

End Function

To get the date you want, call the function:

CountWeeks(Your Date, Number of Days)

Where Your date is the starting date and number of days is the number of days you'd like to add to your initial date.

This function will also calculate the extra Scottish Bank Holiday on 2nd January (I've highlighted it in bold and you can just remove that line).

 

[Blackwidow]

Thanks for your help... it worked brilliantly!

 

[Blackwidow]

CountsWeeks function continued

Mile-O-Phile,

Don't know if you can help me... continuing on from the last one if the Number of Days +Date excluded is greater than 18th July then Excluded until is 1st september and the Due back date is 2nd of September


Any Ideas? Exclusions Team are going mad at me