Date calculation in a query

[NotVeryTechie]

Hi there

This is what I need to do:

Date Field - 2 days
and
Date Field - 1 day

Easy enough, but I need the 2 days and 1 day to be working days only (Monday to Friday). In other words, if something is due on 9 June, then two days prior would be 5 June, not 7 June.

Hope this is not too difficult, I am not very confident yet!

 

[twoplustwo]

Lookup the DateAdd function.

 

[NotVeryTechie]

Ok, I used that, but it is still giving me a date that is two days before the due date, as opposed to two working days. The list of functions in the help doesn't include an expression with weekdays in it.

 

[twoplustwo]

Hmmm, maybe something using the weekday function?

 

[raskew]

Hi -

Copy/paste to a standard module then call as per the example.

Function UpBusDays3(pstart As Date, _
                    pNum As Integer, _
                    Optional pAdd As Boolean = True) As Date
'*******************************************
'Purpose:   Add or subtract business days
'           from a date
'Coded by:  raskew
'Inputs:    1) +) ? UpBusDays3(#2/17/06#, 3, True)
'           2) -) ? UpBusDays3(#2/22/06#, 3, False)
'Output:    1) +) 2/22/06
'           2) -) 2/17/06
'*******************************************
                    
Dim DteHold As Date
Dim i       As Integer
Dim n       As Integer

    DteHold = pstart
    n = pNum
    For i = 1 To n
       If pAdd Then  'add days
          DteHold = DteHold + IIf(WeekDay(DteHold) > 5, 9 - WeekDay(DteHold), 1)
       Else          'subtract days
          DteHold = DteHold - IIf(WeekDay(DteHold) < 3, Choose(WeekDay(DteHold), 2, 3), 1)
       End If
    Next i
    UpBusDays3 = DteHold
    
End Function

Bob

 

[NotVeryTechie]

Thank you so much! Does this go in the Control Source bit?

 

[Mike375]

This might also help as a calculated field in a query

OutPutFiledname: 1+DateDiff("d",[abc],[xyz])-DateDiff("ww",[abc],[xyz],1)-DateDiff("ww",[abc],[xyz],7)

[abc] and [xyz] are date fields. This will give the difference in working days, that is, weekends are excluded.... so you could probably get your result from using that output.