How to add 2 working days to an existing date?

[lwilly]

I am trying to add 2 days to a date field, these two days cannot include weekends or holidays. Any suggestions?<br><br>Thank you,

 

[famousb]

sure, Excel has a thing called NetWorkDays which does this for you, Access does not.&nbsp;&nbsp;so this is a solution that i've used to calculate business days.<br><b><br>If Weekday([yourfieldname]) + 2 = 1 Or Weekday([yourfieldname]) + 2 = 7 Then<br>'Weekday 1 is Sunday, Weekday 7 is Saturday<br>&nbsp;&nbsp;&nbsp;&nbsp;If Weekday([yourfieldname])&nbsp;&nbsp;+ 2 = 1 Then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;BusDays = RecdDate + 3<br>&nbsp;&nbsp;&nbsp;&nbsp;End If<br>&nbsp;&nbsp;&nbsp;&nbsp;If Weekday([yourfieldname]) + 2 = 7 Then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;BusDays = RecdDate + 4<br>&nbsp;&nbsp;&nbsp;&nbsp;End If<br>'adds 3 days if date+2 =Sunday, 4 days if date+2 =Saturday<br>Else<br>&nbsp;&nbsp;&nbsp;&nbsp;BusDays = yourfieldname + 2<br>End If<br>''''''''''''''''''''Brian C. Famous, 2000''''''''''''''''<br></b><br><br>put your date field in where it says &quot;yourfieldname&quot;, and then have another field named [BusDays], or whatever you want to call it (but then you have to change BusDays in the code above too.<br><br>let me know if you have any questions. <p>Brian Famous<br><a href=mailto:bfamous@ncdoi.net>bfamous@ncdoi.net</a><br><a href= > </a><br>

 

[JimConrad]

Brian solved the weekdays portion.<br><br>You may want to create a &quot;Holidays&quot; table and pre-fill it with your company holidays.&nbsp;&nbsp;Then you can check using...<br><br><FONT FACE=monospace><br>Dim HolidayCheck As Variant<br><br>HolidayCheck = 1<br><br>Do Until IsNull(HolidayCheck)<br><br>&nbsp;&nbsp;HolidayCheck = DLookup(&quot;dateHoliday&quot;,&quot;tblHolidays&quot;,&quot;dateHoliday = #&quot; & dateToCheck & &quot;#&quot<br><br>&nbsp;&nbsp;If IsNull(HolidayCheck) Then&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;' Not a holiday<br>&nbsp;&nbsp;Else <br>&nbsp;&nbsp;&nbsp;&nbsp;dateToCheck = dateToCheck + 1<br>&nbsp;&nbsp;End If<br><br>Loop<br></font> <p>Jim Conrad<br><a href=mailto:jconrad3@visteon.com>jconrad3@visteon.com</a><br><a href= > </a><br>

 

[lwilly]

Thanks for the quick response.<br>Now how do I go about using this in an update query so that if it meets my criteria it updates another field to &quot;Y&quot;?<br><br>For instance<br>If [shipdate]=([promisedate]+2workdays) then<br>[twodayslate]=&quot;Y&quot;<br><br>Once I get this to work then I will work on holidays

 

[famousb]

This will calculate promisedate+2 workdays in a query for you:<br><b><br>Workdays: IIf(weekday([promisedate])+2=1, [promisedate]+3, (IIf(weekday([promisedate]+2=7, [promisedate]+4, [promisedate]+2)<br></b><br><br>then in [twodayslate] put<br><b><br>IIf([shipdate]=[workdays],&quot;Y&quot<br></b><br><br>you can make an update query out of that<br><br>this should work, but i didn't test it all. i've used the first part before, and i think this is what you mean. <p>Brian Famous<br><a href=mailto:bfamous@ncdoi.net>bfamous@ncdoi.net</a><br><a href= > </a><br>

 

[gol4]

An additional option<br>newdate: IIf(Weekday([date])+2=1 Or Weekday(([date]+2))=7,Choose([date]+3,0,0,0,0,0,[date]+4),[date]+2)

 

[lwilly]

Thanks for your help!