Calculate the number of days between two dates excluding weekends 1

[jayz12]

I need to Calculate the number of days btwn the day an order was entered into the DDB[date1], to the day the report was run Now(). But I need it to exclude weekends.

How do I display this number in my field[number] of a form?.

I don't need to know the holidays.

My apologies if this has been alreatdy posted

Thanks in advance,
Jay

 

[GingerR]

Check this FAQ (you can do a keyword search to find previous posts)

faq181-261

just take out the code re: holidays, or if you don't understand the VBA, just make a holiday table with nothing in it.

g

 

[jayz12]

Ginger

My problem is how do I use this code in my form?.

I'm creating a form where it displays the number of days in the field [NumofDays] btwn the date a record was entered [Dart1], to the current date Now(). But I want it to exclude weekends & Holidays.

I created the table tblHolidays but the next intructions I'm unsure how to apply this or where to put it:
First, Get the number of days between the dates
NumDays: DeltaDays([StDt], [EndDt])

The following code Public Function DeltaDays etc... , do I put this code in the backend of the current form?, do I need to remane some of the code to reflect my fields.

My apologies, as I'm new to MS Access so any help would be of much appreciation.

 

[GingerR]

ok sorry. here are detailed instructions. get rid of the holiday table; i re-wrote it for you without the holiday stuff:

1) create a new module. paste this into it, close and save the module (doesn't matter what you call it):

Public Function DeltaDays(StartDate As Date) As Integer

'Get the number of workdays between the given dates

Dim Idx As Long
Dim MyDate As Date
Dim NumDays As Long

MyDate = Format(StartDate, "Short Date&quot

For Idx = CLng(StartDate) To Date
Select Case (WeekDay(MyDate))
Case Is = 1 'Sunday
'Do Nothing, it is NOT a Workday

Case Is = 7 'Saturday
'Do Nothing, it is NOT a Workday

Case Else 'Normal Workday
NumDays = NumDays + 1

End Select

MyDate = DateAdd("d", 1, MyDate)

Next Idx

DeltaDays = NumDays

End Function

when i ran this i think it is adding 1 for the OrderEntryDate. if you do not want that, then substitute

MyDate = Format(StartDate, "Short Date&quot + 1

so that it will be the number of days BETWEEN, not INCLUDING, the first day.


2) set your control source:in your form, make the control source of your 'diff' text box be this:

=deltadays([Date1])

what you are doing is 'calling' the function we made and passing your OrderEntryDate (date1) to the function to be calculated upon.

this is assuming your EntryDate is called 'date1' which is what i read in your orig post. if not, change Date1 to whatever your field is called (the field that has the date the ordered was entered).

see if that works. if you want more of an explanation let me know )

have fun--g

 

[jayz12]

Ginger,

Thanks a million for the code, it worked like a gem. And your detailed explanation was very easy to follow.

I hope I can one day return the favor.

Regards,
Jay

 

[jayz12]

Just a correction to your post Ginger,

If you want the number of days to be btwn 2 dates (not including the first day then substitute:

DeltaDays = NumDays

with

DeltaDays = NumDays-1

For some reason MyDate = Format(StartDate, "Short Date&quot + 1
didn't work.

Cheers,
Jay