I know this topic is a veritable dead horse, but I have to ask, because I am unable to find something that is close to my scenario that I can completely understand.
I have a start date field and complete date field.
I need to know how many days, minus holidays and weekends are between the two dates. i already have the table of holidays named "holiday table" with the field being named "Holidaydat e".
I need this to output a number into a field for number of days.
Is there a simple way to do this? Most of the code I have seen is a bit beyond my understanding as I am still a novice.
Does anyone have some code that might do this?
8 25467 ChipR 1,287
Recognized Expert Top Contributor
Here's how I'd subtract the holidays, though I haven't looked at any other posts so there may well be a better way:
listbox.RowSour ce = "SELECT Holidaydate FROM HolidayTable WHERE Holidaydate BETWEEN #" & StartDate & "# AND #" & EndDate & "#;"
numberOfHoliday sToSubtract = listbox.ListCou nt
Hi,
I wrote this little function and it seems to do what you want. It is fairly customisable so you should be able to tweak it for your needs. It assumes you have a table somewhere with dates to exclude.
If you are using it in a query, then use it like: - Somefield: Workingdays([Startfield],[Completefield])
If on a form, place an unbound textbox there and then put this in the form's On Current event: -
'Change all control names to your control names.
-
If isnull(Me.StartDateControlName) or isnull(Me.EndDateControlName) Then
-
Me.TextBoxName = "N/A"
-
Exit Sub
-
Else
-
Me.TextBoxName = WorkingDays(Me.StartDateControlName,Me.EndDateControlName)
-
End If
Place this function inside a module. - Public Function WorkingDays(StartDate As Date, EndDate As Date) As Integer
-
On Error GoTo Err_WorkingDays
-
-
Dim intCount As Integer
-
Dim temp As Integer
-
Dim strWhere As String
-
-
'Set same day as 1 <-- your preference. Though you can't work out averages if they are 0.
-
'Im not checking if this day is a holiday (though in theory you shouldn't have to!).
-
If StartDate = EndDate Then
-
WorkingDays = 1 'Change me to your needs.
-
Exit Function
-
End If
-
-
intCount = 1 ' Now we start counting days. 'If you always want to count the first day, set this to 1.
-
-
Do Until StartDate = EndDate
-
-
'First, we find out if this day is a weekday or a weekend.
-
'If weekday, 1 gets added to the number of days.
-
Select Case Weekday(StartDate)
-
Case Is = 1, 7
-
intCount = intCount 'Weekend, so nothing added.
-
Case Else
-
intCount = intCount + 1
-
End Select
-
-
'Now, if this day was a holiday, we take it back off again!
-
strWhere = "Holidays=#" & StartDate & "#" 'change fieldname.
-
If DCount("Holidays", "tbl_holidays", strWhere) > 0 Then 'Change to your field/table names.
-
intCount = intCount - 1
-
End If
-
-
StartDate = StartDate + 1 'We move to the next day.
-
-
Loop
-
-
WorkingDays = intCount
-
-
Exit_WorkingDays:
-
Exit Function
-
-
Err_WorkingDays:
-
MsgBox Err.Description
-
Resume Exit_WorkingDays
-
-
End Function
-
Gaz :)
i am about to try to use this right now.
Thank you!
@GazMathias
Follow up question,
Can the calculation be altered to process if either date is in a different format? like2/18/2008" and the other is 12/1/2008 3:55:53 PM"?
how might i have the program skip a day if the start date (submitted date) was after 4:59 pm?
i can only get this to work some of the time. usually all i get is overflow errors,
i have no idea what i am doing wrong
FishVal 2,653
Recognized Expert Specialist
[quote] -
strWhere = "Holidays=#" & StartDate & "#" 'change fieldname.
-
If DCount("Holidays", "tbl_holidays", strWhere) > 0 Then 'Change to your field/table names.
-
intCount = intCount - 1
-
End If
-
:D
From some unknown to me reasons this code is everywhere in the net.
I just wonder who was the first to suggest this code.
Wouldn't it be more effective to run DCount once using range from StartDate till EndDate as criteria and holiday table filtered from weekends with a simple query as domain instead of running it on each day in the range?
Kind regards,
Fish.
FishVal 2,653
Recognized Expert Specialist @trixxnixon
Just add 7*60+1 minutes to the start date.
DateAdd() function is just for that.
Regards,
Fish
ADezii 8,834
Recognized Expert Expert @trixxnixon - Create a Table named tblHolidays, with a single Field named DATE (DATE/TIME). Populate this Table with all the Holidays that you wish to exclude, namely 12/25/2008, 1/1/2009, etc.
- Copy and Paste the following code into a Standard Code Module:
- Public Function fExcludeHolidaysAndWeekEnds(dteStartDate As Date, dteEndDate As Date) As Integer
-
Dim intDayDiff As Integer
-
Dim intDayCounter As Integer
-
Dim dteCurrentDate As Date
-
Dim intWeekEndHolDates As Integer
-
-
intDayDiff = DateDiff("d", dteStartDate, dteEndDate)
-
-
For intDayCounter = 0 To intDayDiff
-
dteCurrentDate = DateAdd("d", intDayCounter, dteStartDate)
-
If Weekday(dteCurrentDate) = 7 Or Weekday(dteCurrentDate) = 1 Or DLookup(dteCurrentDate, "tblHolidays", "[Date] = #" & _
-
dteCurrentDate & "#") > 0 Then
-
intWeekEndHolDates = intWeekEndHolDates + 1
-
End If
-
Next
-
-
fExcludeHolidaysAndWeekEnds = intDayDiff - intWeekEndHolDates
-
End Function
- Call the Function ans pass to it the Dates in question.
- Dim intDaysDiff As Integer
-
-
intDaysDiff = fExcludeHolidaysAndWeekEnds(#12/1/2008#, #12/31/2008#)
-
MsgBox intDaysDiff
- The Return Value will be the number of Days between the 2 Dates, minus Weekends and Holidays.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Ralph Freshour |
last post by:
What's a good way to calculate the number of days between two dates in
the following format:
2003-07-15
2003-08-02
I've looked at the PHP date functions but I'm still a bit lost...
|
by: pauly |
last post by:
Hello All,
I have been trying to create a query that extracts data from a table
and calculates the elapsed time between records. The table called
"Imported_table". I need to be able to calculate the difference in
dates when the vin and labourop are the same and not show any other
records except for these and add a new field called days...
|
by: Rustan |
last post by:
Hi
Im using GregorianCalendar to find out the current years week numbers.
When the user chooses a week number in a dropdown i want to show that week
in a table with the corresponding dates.
For example : the user choses week43 (this week)
so somehow i must calculate what date is startdate that week (monday 18th).
How do i do this with c# ?...
|
by: Paul Aspinall |
last post by:
Hi
I want to calculate the difference between 2 dates in C#.
I know there is a function in VB, called DateDiff, but I don't want to ref
the VB library, and want to try to do it natively in C#.
Is there a similar function available in the C# or standard .NET libraries?
Thanks
|
by: b.beeching |
last post by:
Not sure if my subject is entirely accurate but here goes.
I need to calculate the date difference between a date A and a date
B... however date B relies entily on date A.
EG: i have an advert in a paper (The Daily Telegraph say), now i need
to know the next and previos dates that this occured.
I can do this very easily for a one time...
| |
by: rodneyeid |
last post by:
Hi,
I have an attendance machine which saves records in an Access Database in the following format : UserID DATE/TIME Checktype
where if checktype is 0 then its check in and if it is 1 then it check out
I need to calculate number of hours for each userID /day.
I.e UserID =1 DATE/Time= 11/12/2007 12:36 P.M CheckType=0
UserID = 1...
|
by: rahulae |
last post by:
help me with this
I'm able to calculate total working days excluding weekends but how to exclude holidays,is there any other way apart from storing all the holidays in some table and not selecting those or else is there any other option
|
by: aneinander |
last post by:
I'm beginner and getting following error message, please help.
The table contains transaction date, account number, branch, etc. And need to show previous transaction date or next transaction date of the account number next to each record,
then calculate date interval.
Thank you in advance,
Sean,
----error message----
"You tried to execute a...
|
by: nilanjangm |
last post by:
I am using Access 2007. In a table, I have two columns say Start_Date and End_Date of datatype Date/Time (Short Date).
Now, I want to calculate the duration of work in that period assuming 8 hours per day as the working hours, excluding the weekends (Saturdays & Sundays).
I could not get the way to do it. Any help for this please?
...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it. ...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
| |
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |