Calculate the number of work days between two dates with specific days off
in [Help]
|
From: blah on 16 Apr 2010 22:12 I would like to calculate the number of "work days" between two dates; however, the "regular days off" are may not be Sat and Sun and they may not be consecutive. Is there a way to calculate the number of days between two dates that deducts certain "days off"? Thus, it could calculate the number of work days for a person with Mondays and Thursdays off and then calculate the number of work days for a person with Saturdaya and Mondays off. (I would ove to be able to enter the two days off and then get the number of work days between two dates which keeps in mind the days off entered. Any/all ideas are greatly appreciated!! Thank you in advance!!
From: blah on 18 Apr 2010 18:32 On Fri, 16 Apr 2010 20:34:10 -0700, Salad <salad(a)oilandvinegar.com> wrote: >blah(a)blah.com wrote: > >> I would like to calculate the number of "work days" between two dates; >> however, the "regular days off" are may not be Sat and Sun and they >> may not be consecutive. Is there a way to calculate the number of >> days between two dates that deducts certain "days off"? Thus, it >> could calculate the number of work days for a person with Mondays and >> Thursdays off and then calculate the number of work days for a person >> with Saturdaya and Mondays off. (I would ove to be able to enter the >> two days off and then get the number of work days between two dates >> which keeps in mind the days off entered. Any/all ideas are greatly >> appreciated!! >> >> Thank you in advance!! > >I got the following functions at >http://www.mvps.org/access/datetime/date0011.htm > >I modified it slightly. I added intDayOff1/2 to the code. If you look >at the Weekday function Sunday is 1...Saturday 7. So if the days off >are sat/sun, you also pass 6/7, if mon, fri you pass 2/6. If there are >no days off, 0. > >I want the workdays between 4/6 and 4/16/2010 with Mon and Sat off >Ex: ? HowManyWeekDay(#4/6/2010#, Date(),2,7) > 9 >Only Sunday off >? HowManyWeekDay(#4/6/2010#, Date(),1,0) > 11 > >Public Function HowManyWeekDay(FromDate As Date, _ > ToDate As Date, _ > intDateOff1 As Integer, _ > intDateOff2 As Integer, _ > Optional ToDateIsIncluded As Boolean = True) > > HowManyWeekDay = DateDiff("d", FromDate, ToDate) - _ > ToDateIsIncluded - _ > HowManyWD(FromDate, ToDate, intDateOff1) - _ > HowManyWD(FromDate, ToDate, intDateOff2) >End Function > >Public Function HowManyWD(FromDate As Date, _ > ToDate As Date, _ > WD As Integer) As Integer > ' No error handling actually supplied > If WD > 0 Then HowManyWD = _ > DateDiff("ww", FromDate, ToDate, WD) _ > - Int(WD = Weekday(FromDate)) >End Function Thank you very much for the code. I have to admit, I am not an advanced Access user. Your knowledge has been awsome, but it is a bit over my head. Here's my situation: I ahve an Access database that contains a table which has a field for the date a person goes out, a field for the date they return, a drop-down fields for the person's two days off (linked to a table with the days of the week with autonumber for 1 for Sunday through 7 for Saturday). I would like to have a table field calculate the number of days (excluding the particular regular days off) between the date the person goes out and the return date and/or a report that can do the same thing. I'm not sure how to impliment the above code (e.g., if I enter it as a module, how do I use it?) I appreciate your help and patience!! I have learned a decent about Access; however, it is all self-taught. I may not be using the correct terms. Thanks!!
From: blah on 19 Apr 2010 22:17 On Sun, 18 Apr 2010 19:20:11 -0700, Salad <salad(a)oilandvinegar.com> wrote: >blah(a)blah.com wrote: >> On Fri, 16 Apr 2010 20:34:10 -0700, Salad <salad(a)oilandvinegar.com> >> wrote: >> >> >>>blah(a)blah.com wrote: >>> >>> >>>>I would like to calculate the number of "work days" between two dates; >>>>however, the "regular days off" are may not be Sat and Sun and they >>>>may not be consecutive. Is there a way to calculate the number of >>>>days between two dates that deducts certain "days off"? Thus, it >>>>could calculate the number of work days for a person with Mondays and >>>>Thursdays off and then calculate the number of work days for a person >>>>with Saturdaya and Mondays off. (I would ove to be able to enter the >>>>two days off and then get the number of work days between two dates >>>>which keeps in mind the days off entered. Any/all ideas are greatly >>>>appreciated!! >>>> >>>>Thank you in advance!! >>> >>>I got the following functions at >>>http://www.mvps.org/access/datetime/date0011.htm >>> >>>I modified it slightly. I added intDayOff1/2 to the code. If you look >>>at the Weekday function Sunday is 1...Saturday 7. So if the days off >>>are sat/sun, you also pass 6/7, if mon, fri you pass 2/6. If there are >>>no days off, 0. >>> >>>I want the workdays between 4/6 and 4/16/2010 with Mon and Sat off >>>Ex: ? HowManyWeekDay(#4/6/2010#, Date(),2,7) >>> 9 >>>Only Sunday off >>>? HowManyWeekDay(#4/6/2010#, Date(),1,0) >>> 11 >>> >>>Public Function HowManyWeekDay(FromDate As Date, _ >>> ToDate As Date, _ >>> intDateOff1 As Integer, _ >>> intDateOff2 As Integer, _ >>> Optional ToDateIsIncluded As Boolean = True) >>> >>> HowManyWeekDay = DateDiff("d", FromDate, ToDate) - _ >>> ToDateIsIncluded - _ >>> HowManyWD(FromDate, ToDate, intDateOff1) - _ >>> HowManyWD(FromDate, ToDate, intDateOff2) >>>End Function >>> >>>Public Function HowManyWD(FromDate As Date, _ >>> ToDate As Date, _ >>> WD As Integer) As Integer >>> ' No error handling actually supplied >>> If WD > 0 Then HowManyWD = _ >>> DateDiff("ww", FromDate, ToDate, WD) _ >>> - Int(WD = Weekday(FromDate)) >>>End Function >> >> >> >> Thank you very much for the code. I have to admit, I am not an >> advanced Access user. Your knowledge has been awsome, but it is a bit >> over my head. Here's my situation: I ahve an Access database that >> contains a table which has a field for the date a person goes out, a >> field for the date they return, a drop-down fields for the person's >> two days off (linked to a table with the days of the week with >> autonumber for 1 for Sunday through 7 for Saturday). I would like to >> have a table field calculate the number of days (excluding the >> particular regular days off) between the date the person goes out and >> the return date and/or a report that can do the same thing. >> >> I'm not sure how to impliment the above code (e.g., if I enter it as a >> module, how do I use it?) >> >> I appreciate your help and patience!! I have learned a decent about >> Access; however, it is all self-taught. I may not be using the >> correct terms. >> >> Thanks!! > >Is this being done from a table? Or a form? I'll pretend a form. > >First, go to Modules, select New, and copy paste the code from my >routine above into it. Save it. Call it Module1 if you like. If >doesn't matter. Close the module > >Click on Forms. Design a new form and follow along. > >Add two textboxes; FromDate and ToDate. Add two comboboxes >ComboOffDay1 and ComboOffDay2. In the property sheet, under Other, you >change the names to the above. > >Both combo's have a default value of 0. They each display the same two >columns. The first column's values are 0..7. The second columns are >"No Day Off, Monday, Tuesday...Sunday". Hide the first column. You can >do the above by the wizard when you create the combos. You'd type the >values in if you don't use a table and column widths could be 0;1 in the >property sheet. With me so far? > >Now add the last textbox. Call it DaysWorked. Default value is 0. In >the ControlSource field enter the following: > =CalcWorkDays(). > >Ok. Now open the code module for the form. Copy and paste the following. > >Function CalcWorkDays() As Long > If IsDate(Me.FromDate) And IsDate(Me.ToDate) Then > CalcWorkDays = HowManyWeekDay(CDate(Me.FromDate), >CDate(Me.ToDate), Me.DayOff1, Me.DayOff2) > End If >End Function >Private Sub DayOff1_AfterUpdate() > Me.DaysWorked.Requery >End Sub >Private Sub DayOff2_AfterUpdate() > Me.DaysWorked.Requery >End Sub >Private Sub FromDate_AfterUpdate() > Me.DaysWorked.Requery >End Sub >Private Sub ToDate_AfterUpdate() > Me.DaysWorked.Requery >End Sub > >If you open the property sheet, click on Events, and then click on each >field except DaysWorked you'll see [Event Procedure]. If you don't, >click the down arrow in the AfterUpdate event. > >OK. Save the form and run/open it. Now enter some dates, change the >off day values, and the textbox will be updated with the correct nunmber >of workdays. > >Does it work as you expect. BTW, simply reading this without doing it >will be fruitless for you. > >Here's the deal. You need a starting day and an ending day. You need 2 >values 0..7, that represent dayoff 1 & 2. If you have that information, >you can even create a query to calc it. > >I'll pretend you have these fields in a table and that these are the >field names. In the query builder drop your four fields into the >columns; FromDate, ToDate, DayOff1 (DO1) , DayOff2 (DO1). I used DO1 so >the line below doesn't wrap. DO1/2 are 0..7 to represent days. Then >enter the following > DaysWorked : HowManyWeekDay(CDate(FromDate), CDate(ToDate), DO1, DO2) > >Now if you save and run the query, the 5th column will show the days worked. Thank you. I actually have the data saved in two tables (one has the th date the person went "out" and the date the person "returned" and the second has the days off (two fields with Sunday = 1 through Saturday = 7). I would like to have a field in the first table (if possible) that would calculate the number of work days between the the date the person went "out" and the date the person "returned" minus th two days off. I wouldlike to add to tis by saying that the date the person returned may be added later....can this be ok with the return date being blank until it is entered? THANK YOU, Salad!!
|
Pages: 1 Prev: Query outlook 2007 folder with Access 2007 Next: Should I completely abandon Requery? |