# Date calculation question

On a form I have a field named "txtAck_Date".  Also on the form I have a field named "txtTotalLTdays" which is a number field.  Then there is a 3rd field on the form named "txtProdDate".

I need the field "txtProdDate" to be a calculated field.  When the user enters a date in "txtAck_Date" then "txtProdDate" has to be "txtAck_Date" MINUS the days in "txtTotalLTdays" BUT NOT INCLUDING WEEKENDS (Saturdays or Sundays).

For example, using February of this year, if the user enters 2/21/13 in "txtAck_Date" I need to have "txtProdDate" back up by 20 days if "txtTotalLtDays" is 20.  Therefore "txtProdDate" would be automatically calculated to be 1/24/13.

I can't figure out what the afterupdate event code would be for "txtAck_Date".
###### Who is Participating?

Commented:
Okay -- I think you'll have to use a custom function.

Try placing this in a separate module:

``````Function DateDiffNoWeekends(dt As Date, intNumDays As Integer) As Date
Dim I As Integer
Dim dtNew As Date
I = 0
dtNew = dt
Do Until I = intNumDays
dtNew = dtNew - 1
Do Until Weekday(dtNew) <> 7 And Weekday(dtNew) <> 1
dtNew = dtNew - 1
Loop
I = I + 1
Loop
DateDiffNoWeekends = dtNew

End Function
``````

And call it from your after update event like this:

`````` Me.txtProdDate = DateDiffNoWeekends( Me.txtShipDate,   Me.txtTotalLTdays)
``````

(Im assuming that you will be passing a positive number in txtTotalLTdays)
0

Commented:
try this:

txtProdDate =  DateAdd( "w", -20 , txtAck_Date)
0

Author Commented:
I'm sorry.  One thing all should know is that "txtTotalLtDays" is also a calculated field.  I used 20 as an example but it could be any number.

Using:

txtProdDate =  DateAdd( "w", -20 , txtAck_Date)

didn't work though.  When I enter 2/21/13 in the "txtShipDate" field 2/1/13 populated "txtProdDate" and it should be 1/24/13 becuase it has to NOT include weekend days.
0

Author Commented:
I tried:

And I still get 2/1/13 when I enter 2/21/13 instead of getting 1/24/13.

??
0

Author Commented:
I meant to type:

I tried:

And I still get 2/1/13 when I enter 2/21/13 instead of getting 1/24/13.

??
0

Author Commented:
To mbizup:

A perfect work of art!  Thank you.
0

Commented:
Thanks, Steve!  Glad that helped out :-)

The next reasonable 'step' in these questions is very often "how do I exclude holidays?".

That block of code is a starting point... and if you needed to exclude holidays, you'd have to create a holiday table and use DLookups from that table or some other method within the loop to skip over dates that are included in your holiday table.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.