Get first working date after specific date.

[LB79]

Hi All,

I've searched Google for a bit and can’t find quite what I'm looking for.
There are a variety of date functions but I specifically want to show the date or the first working day after the 25th of each month.
Eg:
if 25 Oct is a Monday then result = 25 Oct
if 25 Oct is a Sunday then result = 26 Oct

Thanks for any advice

 

[namliam]

Do you need to take into account national holidays and stuff like Xmass ???
If not... it shoudlnt be to hard to work out something like:
If(weekday(yourdate,7) <3, -1 * Weekday(yourdate,7)+3,0) + Yourdate

 

[missinglinq]

Replacing OriginalDate and CalculatedDate with your actual control names:

Private Sub OriginalDate_AfterUpdate()
 
 Select Case True
 
 Case (Weekday(DateSerial(Year(Me.OriginalDate), Month(Me.OriginalDate), 25)) > 1) And (Weekday(DateSerial(Year(Me.OriginalDate), Month(Me.OriginalDate), 25)) < 7)
   
  Me.CalculatedDate = DateSerial(Year(Me.OriginalDate), Month(Me.OriginalDate), 25)
 
 Case (Weekday(DateSerial(Year(Me.OriginalDate), Month(Me.OriginalDate), 26)) > 1) And (Weekday(DateSerial(Year(Me.OriginalDate), Month(Me.OriginalDate), 26)) < 7)
 
  Me.CalculatedDate = DateSerial(Year(Me.OriginalDate), Month(Me.OriginalDate), 26)
 
Case (Weekday(DateSerial(Year(Me.OriginalDate), Month(Me.OriginalDate), 27)) > 1) And (Weekday(DateSerial(Year(Me.OriginalDate), Month(Me.OriginalDate), 27)) < 7)
 
  Me.CalculatedDate = DateSerial(Year(Me.OriginalDate), Month(Me.OriginalDate), 27)
 
End Select

End Sub

Linq ;0)>

 

[MarkK]

I'd approach it like this . . .

Function GetNthDay(d As Date, n as integer) As Date
[COLOR="Green"]'   Returns a date that is the Nth day of the given month and year[/COLOR]
    GetNthDay = DateSerial(Year(d), Month(d), n)
End Function

Function GetNextWeekday(d1 As Date) As Date
[COLOR="Green"]'   Returns the first weekday that occurs on or after the given date
[/COLOR]    GetNextWeekday = d1               [COLOR="Green"]'in most cases this is correct[/COLOR]
    Select Case Weekday(d1)           [COLOR="Green"]'but[/COLOR]
        Case 7                        [COLOR="Green"]'for Saturday[/COLOR]
            GetNextWeekday = d1 + 2   [COLOR="Green"]'add two days[/COLOR]
        Case 1                        [COLOR="Green"]'for Sunday[/COLOR]
            GetNextWeekday = d1 + 1   [COLOR="Green"]'add one[/COLOR]
    End Select
End Function

. . . so you have functions you can re-use, and call it like . . .

Me.CalculatedDate = GetNextWeekday(GetNthDay(Me.OriginalDate, 25))

 

[LB79]

Great suggestions guys - many thanks